Imports Microsoft.VisualBasic
Imports System.Data
Imports System.Data.OleDb
Imports System.Configuration.ConfigurationSettings
Imports System.Diagnostics
Imports DAOMain

Namespace DAOReport

#Region "report_mstr"

    <Serializable()> _
    Public Class REPORT_MSTR_VO

        Public REPORT_CD As String
        Public REPORT_NAME As String
        Public REPORT_BTN_ID As String
        Public REPORT_IMG_ID As String
        Public REPORT_TABLE As String
        Public REPORT_PATH As String
        Public REPORT_URL As String

        Public Enum Enum_REPORT_MSTR As Short
            NONE = 0
            report_cd = 1
            report_name = 2
            report_btn_id = 4
            report_img_id = 8
            report_table = 16
            report_path = 32
            report_url = 64
        End Enum

    End Class

    Public Class REPORT_MSTR_DAO
        Inherits CommonDAO

        Public Function Search(ByVal Value As report_mstr_VO, Optional ByVal ExcludeValue As report_mstr_VO.Enum_report_mstr = 0, Optional ByVal SQLLike As Boolean = False) As Data.Datatable
            Dim daoMain As New Polydao.Common
            Dim cnnSearch As New OleDbConnection
            Dim dtSearch As New Data.DataTable

            Try
                daoMain.ConnectionString = Me.ConnectionString
                cnnSearch = daoMain.OpenConnection

                Dim cmdSearch As New OLEDBCommand

                With cmdSearch
                    .Connection = cnnSearch
                    .CommandText = "SELECT * FROM report_mstr " & QueryBuilder(Value, QueryType.SelectType, ExcludeValue, , SQLLike)
                End With

                Call ParamBuilder(cmdSearch, Value, ExcludeValue)

                Dim daSearch As New OleDbDataAdapter(cmdSearch)

                daSearch.Fill(dtSearch)

            Catch ex As Exception

                Debug.WriteLine(ex.Message)

            Finally
                daoMain.CloseConnection(cnnSearch)

            End Try

            Return dtSearch
        End Function

        Private Function QueryBuilder(ByVal Value As REPORT_MSTR_VO, Optional ByVal QueryType As QueryType = 0, _
         Optional ByVal ExcludeFlag As REPORT_MSTR_VO.Enum_report_mstr = REPORT_MSTR_VO.Enum_report_mstr.NONE, Optional ByVal TableAlias As String = "", Optional ByVal SQLLike As Boolean = False, _
         Optional ByVal IncludeBlanks As Boolean = True) As String

            Dim sbQuery As New StringBuilder
            Dim shrtFlag As Short

            With Value
                If Not isnothing(.REPORT_CD) Then shrtFlag += REPORT_MSTR_VO.Enum_report_mstr.REPORT_CD
                If Not isnothing(.REPORT_NAME) Then shrtFlag += REPORT_MSTR_VO.Enum_report_mstr.REPORT_NAME
                If Not isnothing(.REPORT_BTN_ID) Then shrtFlag += REPORT_MSTR_VO.Enum_report_mstr.REPORT_BTN_ID
                If Not isnothing(.REPORT_IMG_ID) Then shrtFlag += REPORT_MSTR_VO.Enum_report_mstr.REPORT_IMG_ID
                If Not isnothing(.REPORT_TABLE) Then shrtFlag += REPORT_MSTR_VO.Enum_report_mstr.REPORT_TABLE
                If Not isnothing(.REPORT_PATH) Then shrtFlag += REPORT_MSTR_VO.Enum_report_mstr.REPORT_PATH
                If Not isnothing(.REPORT_URL) Then shrtFlag += REPORT_MSTR_VO.Enum_report_mstr.REPORT_URL
            End With
            If shrtFlag <> 0 Then
                Dim bytCount As Byte

                If ExcludeFlag <> REPORT_MSTR_VO.Enum_report_mstr.None Then shrtFlag -= ExcludeFlag
                With sbQuery

                    Select Case QueryType
                        Case QueryType.SelectType  ' Where
                            .Append("WHERE ")
                        Case QueryType.UpdateType  'Update
                            .Append("UPDATE report_mstr SET ")
                        Case QueryType.InsertType  'Insert
                            .Append("INSERT INTO report_mstr (")
                        Case QueryType.DeleteType   'Delete
                            .Append("DELETE FROM report_mstr WHERE ")
                    End Select

                    Do While shrtFlag > 0
                        If CheckBit(shrtFlag, REPORT_MSTR_VO.Enum_report_mstr.REPORT_CD) Then
                            .Append("REPORT_CD")
                            shrtFlag -= REPORT_MSTR_VO.Enum_report_mstr.REPORT_CD

                        ElseIf CheckBit(shrtFlag, REPORT_MSTR_VO.Enum_report_mstr.REPORT_NAME) Then
                            .Append("REPORT_NAME")
                            shrtFlag -= REPORT_MSTR_VO.Enum_report_mstr.REPORT_NAME

                        ElseIf CheckBit(shrtFlag, REPORT_MSTR_VO.Enum_report_mstr.REPORT_BTN_ID) Then
                            .Append("REPORT_BTN_ID")
                            shrtFlag -= REPORT_MSTR_VO.Enum_report_mstr.REPORT_BTN_ID

                        ElseIf CheckBit(shrtFlag, REPORT_MSTR_VO.Enum_report_mstr.REPORT_IMG_ID) Then
                            .Append("REPORT_IMG_ID")
                            shrtFlag -= REPORT_MSTR_VO.Enum_report_mstr.REPORT_IMG_ID

                        ElseIf CheckBit(shrtFlag, REPORT_MSTR_VO.Enum_report_mstr.REPORT_TABLE) Then
                            .Append("REPORT_TABLE")
                            shrtFlag -= REPORT_MSTR_VO.Enum_report_mstr.REPORT_TABLE

                        ElseIf CheckBit(shrtFlag, REPORT_MSTR_VO.Enum_report_mstr.REPORT_PATH) Then
                            .Append("REPORT_PATH")
                            shrtFlag -= REPORT_MSTR_VO.Enum_report_mstr.REPORT_PATH

                        ElseIf CheckBit(shrtFlag, REPORT_MSTR_VO.Enum_report_mstr.REPORT_URL) Then
                            .Append("REPORT_URL")
                            shrtFlag -= REPORT_MSTR_VO.Enum_report_mstr.REPORT_URL

                        End If

                        Select Case QueryType
                            Case QueryType.SelectType, QueryType.DeleteType  ' Where Clause
                                Select Case SQLLike
                                    Case False
                                        .Append(" = ? AND ")

                                    Case True
                                        .Append(" LIKE ? AND ")

                                End Select

                            Case QueryType.UpdateType  ' Update
                                .Append(" = ?, ")

                            Case QueryType.InsertType  ' Insert
                                .Append(", ")
                                bytCount += 1

                            Case Else 'bypass
                                .Append(" AND ")
                                QueryType = 0

                        End Select
                    Loop

                    Select Case QueryType
                        Case QueryType.SelectType, QueryType.DeleteType  'Where Clause
                            .Replace(" AND ", "", .Length - 6, 6)
                            .Append(";")

                        Case QueryType.UpdateType   ' Update
                            .Replace("?, ", "? ", .Length - 3, 3)

                        Case QueryType.InsertType  ' Insert
                            .Replace(", ", ") VALUES (", .Length - 2, 2)
                            .Append("#", bytCount)
                            .Replace("#", "?,")
                            .Replace("?,", "?);", .Length - 2, 2)
                    End Select
                End With
            End If
            Return sbQuery.ToString
        End Function

        Private Sub ParamBuilder(ByRef Command As OLEDBCommand, ByVal Value As report_mstr_VO, Optional ByVal QueryType As QueryType = 0, Optional ByVal ExcludeValue As report_mstr_VO.Enum_report_mstr = report_mstr_VO.Enum_report_mstr.None)
            With Command.Parameters
                If Not isnothing(Value.REPORT_CD) And Not CheckBit(ExcludeValue, report_mstr_VO.Enum_report_mstr.REPORT_CD) Then .AddWithValue("@report_cd", Value.REPORT_CD)
                If Not isnothing(Value.REPORT_NAME) And Not CheckBit(ExcludeValue, report_mstr_VO.Enum_report_mstr.REPORT_NAME) Then .AddWithValue("@report_name", Value.REPORT_NAME)
                If Not isnothing(Value.REPORT_BTN_ID) And Not CheckBit(ExcludeValue, report_mstr_VO.Enum_report_mstr.REPORT_BTN_ID) Then .AddWithValue("@report_btn_id", Value.REPORT_BTN_ID)
                If Not isnothing(Value.REPORT_IMG_ID) And Not CheckBit(ExcludeValue, report_mstr_VO.Enum_report_mstr.REPORT_IMG_ID) Then .AddWithValue("@report_img_id", Value.REPORT_IMG_ID)
                If Not isnothing(Value.REPORT_TABLE) And Not CheckBit(ExcludeValue, report_mstr_VO.Enum_report_mstr.REPORT_TABLE) Then .AddWithValue("@report_table", Value.REPORT_TABLE)
                If Not isnothing(Value.REPORT_PATH) And Not CheckBit(ExcludeValue, report_mstr_VO.Enum_report_mstr.REPORT_PATH) Then .AddWithValue("@report_path", Value.REPORT_PATH)
                If Not isnothing(Value.REPORT_URL) And Not CheckBit(ExcludeValue, report_mstr_VO.Enum_report_mstr.REPORT_URL) Then .AddWithValue("@report_url", Value.REPORT_URL)
            End With
        End Sub
        Public Function GetQuery(ByVal Value As report_mstr_VO, Optional ByVal ExcludeValue As report_mstr_VO.Enum_report_mstr = 0, Optional ByVal SQLLike As Boolean = False) As String
            Dim strReturn As String = "SELECT * FROM report_mstr " & QueryBuilder(Value, QueryType.SelectType, ExcludeValue, , SQLLike)
            Return strReturn
        End Function

    End Class

#End Region

#Region "vw_rpt_aging_unpaid_premium"

    <Serializable()> _
    Public Class VW_RPT_AGING_UNPAID_PREMIUM_VO

        Public NAME As String
        Public POLICYNO As String
        Public PREMIUMDUE As Double = -1
        Public CONTRIBUTION_DUE_DT As Date
        Public ADVISOR As String
        Public PROVIDER_DESC As String
        Public PRDT_DESC As String
        Public ADVISOR_CD As String
        Public AGING_RANGE As Integer = -1

        Public Enum Enum_VW_RPT_AGING_UNPAID_PREMIUM As Short
            NONE = 0
            Name = 1
            PolicyNo = 2
            PremiumDue = 4
            contribution_due_dt = 8
            Advisor = 16
            provider_desc = 32
            prdt_desc = 64
            Advisor_Cd = 128
            Aging_Range = 256
        End Enum

    End Class

    Public Class VW_RPT_AGING_UNPAID_PREMIUM_DAO
        Inherits CommonDAO

        Public Function Search(ByVal Value As VW_RPT_AGING_UNPAID_PREMIUM_VO, _
                                ByVal currDate As String, _
                                Optional ByVal ExcludeValue As VW_RPT_AGING_UNPAID_PREMIUM_VO.Enum_VW_RPT_AGING_UNPAID_PREMIUM = 0, _
                                Optional ByVal SQLLike As Boolean = False) As Data.DataTable

            Dim daoMain As New Polydao.Common
            Dim cnnSearch As New OleDbConnection
            Dim dtSearch As New Data.DataTable

            Try
                daoMain.ConnectionString = Me.ConnectionString
                cnnSearch = daoMain.OpenConnection

                Dim cmdSearch As New OleDbCommand

                With cmdSearch
                    .Connection = cnnSearch
                    .CommandText = "SELECT convert(datetime, '" & currDate & "') as currDate, * FROM vw_rpt_aging_unpaid_premium " & QueryBuilder(Value, QueryType.SelectType, ExcludeValue, , SQLLike)
                End With

                Call ParamBuilder(cmdSearch, Value, ExcludeValue)

                Dim daSearch As New OleDbDataAdapter(cmdSearch)

                daSearch.Fill(dtSearch)

            Catch ex As Exception

                Debug.WriteLine(ex.Message)

            Finally
                daoMain.CloseConnection(cnnSearch)

            End Try

            Return dtSearch
        End Function

        Private Function QueryBuilder(ByVal Value As VW_RPT_AGING_UNPAID_PREMIUM_VO, Optional ByVal QueryType As QueryType = 0, _
         Optional ByVal ExcludeFlag As VW_RPT_AGING_UNPAID_PREMIUM_VO.Enum_VW_RPT_AGING_UNPAID_PREMIUM = VW_RPT_AGING_UNPAID_PREMIUM_VO.Enum_VW_RPT_AGING_UNPAID_PREMIUM.NONE, Optional ByVal TableAlias As String = "", Optional ByVal SQLLike As Boolean = False, _
         Optional ByVal IncludeBlanks As Boolean = True) As String

            Dim sbQuery As New StringBuilder
            Dim shrtFlag As Short

            With Value
                If Not IsNothing(.NAME) Then shrtFlag += VW_RPT_AGING_UNPAID_PREMIUM_VO.Enum_VW_RPT_AGING_UNPAID_PREMIUM.Name
                If Not IsNothing(.POLICYNO) Then shrtFlag += VW_RPT_AGING_UNPAID_PREMIUM_VO.Enum_VW_RPT_AGING_UNPAID_PREMIUM.PolicyNo
                If .PREMIUMDUE >= 0 Then shrtFlag += VW_RPT_AGING_UNPAID_PREMIUM_VO.Enum_VW_RPT_AGING_UNPAID_PREMIUM.PremiumDue
                If Year(.CONTRIBUTION_DUE_DT) > 1 Then shrtFlag += VW_RPT_AGING_UNPAID_PREMIUM_VO.Enum_VW_RPT_AGING_UNPAID_PREMIUM.contribution_due_dt
                If Not IsNothing(.ADVISOR) Then shrtFlag += VW_RPT_AGING_UNPAID_PREMIUM_VO.Enum_VW_RPT_AGING_UNPAID_PREMIUM.Advisor
                If Not IsNothing(.PROVIDER_DESC) Then shrtFlag += VW_RPT_AGING_UNPAID_PREMIUM_VO.Enum_VW_RPT_AGING_UNPAID_PREMIUM.provider_desc
                If Not IsNothing(.PRDT_DESC) Then shrtFlag += VW_RPT_AGING_UNPAID_PREMIUM_VO.Enum_VW_RPT_AGING_UNPAID_PREMIUM.prdt_desc
                If Not IsNothing(.ADVISOR_CD) Then shrtFlag += VW_RPT_AGING_UNPAID_PREMIUM_VO.Enum_VW_RPT_AGING_UNPAID_PREMIUM.Advisor_Cd
                If .AGING_RANGE >= 0 Then shrtFlag += VW_RPT_AGING_UNPAID_PREMIUM_VO.Enum_VW_RPT_AGING_UNPAID_PREMIUM.Aging_Range
            End With
            If shrtFlag <> 0 Then
                Dim bytCount As Byte

                If ExcludeFlag <> VW_RPT_AGING_UNPAID_PREMIUM_VO.Enum_VW_RPT_AGING_UNPAID_PREMIUM.NONE Then shrtFlag -= ExcludeFlag
                With sbQuery

                    Select Case QueryType
                        Case QueryType.SelectType  ' Where
                            .Append("WHERE ")
                        Case QueryType.UpdateType  'Update
                            .Append("UPDATE vw_rpt_aging_unpaid_premium SET ")
                        Case QueryType.InsertType  'Insert
                            .Append("INSERT INTO vw_rpt_aging_unpaid_premium (")
                        Case QueryType.DeleteType   'Delete
                            .Append("DELETE FROM vw_rpt_aging_unpaid_premium WHERE ")
                    End Select

                    Do While shrtFlag > 0
                        If CheckBit(shrtFlag, VW_RPT_AGING_UNPAID_PREMIUM_VO.Enum_VW_RPT_AGING_UNPAID_PREMIUM.Name) Then
                            .Append("NAME")
                            shrtFlag -= VW_RPT_AGING_UNPAID_PREMIUM_VO.Enum_VW_RPT_AGING_UNPAID_PREMIUM.Name

                        ElseIf CheckBit(shrtFlag, VW_RPT_AGING_UNPAID_PREMIUM_VO.Enum_VW_RPT_AGING_UNPAID_PREMIUM.PolicyNo) Then
                            .Append("POLICYNO")
                            shrtFlag -= VW_RPT_AGING_UNPAID_PREMIUM_VO.Enum_VW_RPT_AGING_UNPAID_PREMIUM.PolicyNo

                        ElseIf CheckBit(shrtFlag, VW_RPT_AGING_UNPAID_PREMIUM_VO.Enum_VW_RPT_AGING_UNPAID_PREMIUM.PremiumDue) Then
                            .Append("PREMIUMDUE")
                            shrtFlag -= VW_RPT_AGING_UNPAID_PREMIUM_VO.Enum_VW_RPT_AGING_UNPAID_PREMIUM.PremiumDue

                        ElseIf CheckBit(shrtFlag, VW_RPT_AGING_UNPAID_PREMIUM_VO.Enum_VW_RPT_AGING_UNPAID_PREMIUM.contribution_due_dt) Then
                            .Append("CONTRIBUTION_DUE_DT")
                            shrtFlag -= VW_RPT_AGING_UNPAID_PREMIUM_VO.Enum_VW_RPT_AGING_UNPAID_PREMIUM.contribution_due_dt

                        ElseIf CheckBit(shrtFlag, VW_RPT_AGING_UNPAID_PREMIUM_VO.Enum_VW_RPT_AGING_UNPAID_PREMIUM.Advisor) Then
                            .Append("ADVISOR")
                            shrtFlag -= VW_RPT_AGING_UNPAID_PREMIUM_VO.Enum_VW_RPT_AGING_UNPAID_PREMIUM.Advisor

                        ElseIf CheckBit(shrtFlag, VW_RPT_AGING_UNPAID_PREMIUM_VO.Enum_VW_RPT_AGING_UNPAID_PREMIUM.provider_desc) Then
                            .Append("PROVIDER_DESC")
                            shrtFlag -= VW_RPT_AGING_UNPAID_PREMIUM_VO.Enum_VW_RPT_AGING_UNPAID_PREMIUM.provider_desc

                        ElseIf CheckBit(shrtFlag, VW_RPT_AGING_UNPAID_PREMIUM_VO.Enum_VW_RPT_AGING_UNPAID_PREMIUM.prdt_desc) Then
                            .Append("PRDT_DESC")
                            shrtFlag -= VW_RPT_AGING_UNPAID_PREMIUM_VO.Enum_VW_RPT_AGING_UNPAID_PREMIUM.prdt_desc

                        ElseIf CheckBit(shrtFlag, VW_RPT_AGING_UNPAID_PREMIUM_VO.Enum_VW_RPT_AGING_UNPAID_PREMIUM.Advisor_Cd) Then
                            .Append("ADVISOR_CD")
                            shrtFlag -= VW_RPT_AGING_UNPAID_PREMIUM_VO.Enum_VW_RPT_AGING_UNPAID_PREMIUM.Advisor_Cd

                        ElseIf CheckBit(shrtFlag, VW_RPT_AGING_UNPAID_PREMIUM_VO.Enum_VW_RPT_AGING_UNPAID_PREMIUM.Aging_Range) Then
                            .Append("AGING_RANGE")
                            shrtFlag -= VW_RPT_AGING_UNPAID_PREMIUM_VO.Enum_VW_RPT_AGING_UNPAID_PREMIUM.Aging_Range

                        End If

                        Select Case QueryType
                            Case QueryType.SelectType, QueryType.DeleteType  ' Where Clause
                                Select Case SQLLike
                                    Case False
                                        .Append(" = ? AND ")

                                    Case True
                                        .Append(" LIKE ? AND ")

                                End Select

                            Case QueryType.UpdateType  ' Update
                                .Append(" = ?, ")

                            Case QueryType.InsertType  ' Insert
                                .Append(", ")
                                bytCount += 1

                            Case Else 'bypass
                                .Append(" AND ")
                                QueryType = 0

                        End Select
                    Loop

                    Select Case QueryType
                        Case QueryType.SelectType, QueryType.DeleteType  'Where Clause
                            .Replace(" AND ", "", .Length - 6, 6)
                            .Append(";")

                        Case QueryType.UpdateType   ' Update
                            .Replace("?, ", "? ", .Length - 3, 3)

                        Case QueryType.InsertType  ' Insert
                            .Replace(", ", ") VALUES (", .Length - 2, 2)
                            .Append("#", bytCount)
                            .Replace("#", "?,")
                            .Replace("?,", "?);", .Length - 2, 2)
                    End Select
                End With
            End If
            Return sbQuery.ToString
        End Function

        Private Sub ParamBuilder(ByRef Command As OleDbCommand, ByVal Value As VW_RPT_AGING_UNPAID_PREMIUM_VO, Optional ByVal QueryType As QueryType = 0, Optional ByVal ExcludeValue As VW_RPT_AGING_UNPAID_PREMIUM_VO.Enum_VW_RPT_AGING_UNPAID_PREMIUM = VW_RPT_AGING_UNPAID_PREMIUM_VO.Enum_VW_RPT_AGING_UNPAID_PREMIUM.NONE)
            With Command.Parameters
                If Not IsNothing(Value.NAME) And Not CheckBit(ExcludeValue, VW_RPT_AGING_UNPAID_PREMIUM_VO.Enum_VW_RPT_AGING_UNPAID_PREMIUM.Name) Then .AddWithValue("@Name", Value.NAME)
                If Not IsNothing(Value.POLICYNO) And Not CheckBit(ExcludeValue, VW_RPT_AGING_UNPAID_PREMIUM_VO.Enum_VW_RPT_AGING_UNPAID_PREMIUM.PolicyNo) Then .AddWithValue("@PolicyNo", Value.POLICYNO)
                If Value.PREMIUMDUE >= 0 And Not CheckBit(ExcludeValue, VW_RPT_AGING_UNPAID_PREMIUM_VO.Enum_VW_RPT_AGING_UNPAID_PREMIUM.PremiumDue) Then .AddWithValue("@PremiumDue", Value.PREMIUMDUE)
                If Year(Value.CONTRIBUTION_DUE_DT) > 1 And Not CheckBit(ExcludeValue, VW_RPT_AGING_UNPAID_PREMIUM_VO.Enum_VW_RPT_AGING_UNPAID_PREMIUM.contribution_due_dt) Then .AddWithValue("@contribution_due_dt", Value.CONTRIBUTION_DUE_DT)
                If Not IsNothing(Value.ADVISOR) And Not CheckBit(ExcludeValue, VW_RPT_AGING_UNPAID_PREMIUM_VO.Enum_VW_RPT_AGING_UNPAID_PREMIUM.Advisor) Then .AddWithValue("@Advisor", Value.ADVISOR)
                If Not IsNothing(Value.PROVIDER_DESC) And Not CheckBit(ExcludeValue, VW_RPT_AGING_UNPAID_PREMIUM_VO.Enum_VW_RPT_AGING_UNPAID_PREMIUM.provider_desc) Then .AddWithValue("@provider_desc", Value.PROVIDER_DESC)
                If Not IsNothing(Value.PRDT_DESC) And Not CheckBit(ExcludeValue, VW_RPT_AGING_UNPAID_PREMIUM_VO.Enum_VW_RPT_AGING_UNPAID_PREMIUM.prdt_desc) Then .AddWithValue("@prdt_desc", Value.PRDT_DESC)
                If Not IsNothing(Value.ADVISOR_CD) And Not CheckBit(ExcludeValue, VW_RPT_AGING_UNPAID_PREMIUM_VO.Enum_VW_RPT_AGING_UNPAID_PREMIUM.Advisor_Cd) Then .AddWithValue("@Advisor_Cd", Value.ADVISOR_CD)
                If Value.AGING_RANGE >= 0 And Not CheckBit(ExcludeValue, VW_RPT_AGING_UNPAID_PREMIUM_VO.Enum_VW_RPT_AGING_UNPAID_PREMIUM.Aging_Range) Then .AddWithValue("@Aging_Range", Value.AGING_RANGE)
            End With
        End Sub
        Public Function GetQuery(ByVal Value As VW_RPT_AGING_UNPAID_PREMIUM_VO, Optional ByVal ExcludeValue As VW_RPT_AGING_UNPAID_PREMIUM_VO.Enum_VW_RPT_AGING_UNPAID_PREMIUM = 0, Optional ByVal SQLLike As Boolean = False) As String
            Dim strReturn As String = "SELECT * FROM vw_rpt_aging_unpaid_premium " & QueryBuilder(Value, QueryType.SelectType, ExcludeValue, , SQLLike)
            Return strReturn
        End Function

    End Class

#End Region

#Region "vw_rpt_birthday_list"

    <Serializable()> _
    Public Class VW_RPT_BIRTHDAY_LIST_VO

        Public CLIENTNAME As String
        Public BIRTHDATE As Date
        Public BIRTHDATE_END As Date
        Public BIRTHDAY As Date
        Public BIRTHDAY_END As Date
        Public AGE As Double = -1
        Public TYPE As String
        Public ADVISOR As String
        Public POLICY_NO As String
        Public PROVIDER_DESC As String
        Public PRDT_DESC As String
        Public ADVISOR_CD As String
        Public POLICY_SEQ_CD As String
        Public CLIENT_CD As String
        Public POLICY_OWNER As String
        Public TEL_NO As String
        Public POLICY_ST As String
        Public BIRTHMONTH As Integer = -1

        Public Enum Enum_vw_rpt_birthday_list As Integer
            NONE = 0
            ClientName = 1
            birthdate = 2
            birthdate_END = 4
            Birthday = 8
            Birthday_END = 16
            Age = 32
            Type = 64
            advisor = 128
            policy_no = 256
            provider_desc = 512
            prdt_desc = 1024
            Advisor_Cd = 2048
            policy_seq_cd = 4096
            client_cd = 8192
            policy_owner = 16384
            tel_no = 32768
            Policy_St = 65536
            birthmonth = 131072
        End Enum

    End Class

    Public Class VW_RPT_BIRTHDAY_LIST_DAO
        Inherits CommonDAO

        Public Function Search(ByVal Value As vw_rpt_birthday_list_VO, Optional ByVal ExcludeValue As vw_rpt_birthday_list_VO.Enum_vw_rpt_birthday_list = 0, Optional ByVal SQLLike As Boolean = False) As Data.Datatable
            Dim daoMain As New Polydao.Common
            Dim cnnSearch As New OleDbConnection
            Dim dtSearch As New Data.DataTable

            Try
                daoMain.ConnectionString = Me.ConnectionString
                cnnSearch = daoMain.OpenConnection

                Dim cmdSearch As New OLEDBCommand

                With cmdSearch
                    .Connection = cnnSearch
                    .CommandText = "SELECT * FROM vw_rpt_birthday_list " & QueryBuilder(Value, QueryType.SelectType, ExcludeValue, , SQLLike)
                End With

                Call ParamBuilder(cmdSearch, Value, ExcludeValue)

                Dim daSearch As New OleDbDataAdapter(cmdSearch)

                daSearch.Fill(dtSearch)

            Catch ex As Exception

                Debug.WriteLine(ex.Message)

            Finally
                daoMain.CloseConnection(cnnSearch)

            End Try

            Return dtSearch
        End Function

        Private Function QueryBuilder(ByVal Value As VW_RPT_BIRTHDAY_LIST_VO, Optional ByVal QueryType As QueryType = 0, _
         Optional ByVal ExcludeFlag As VW_RPT_BIRTHDAY_LIST_VO.Enum_vw_rpt_birthday_list = VW_RPT_BIRTHDAY_LIST_VO.Enum_vw_rpt_birthday_list.NONE, Optional ByVal TableAlias As String = "", Optional ByVal SQLLike As Boolean = False, _
         Optional ByVal IncludeBlanks As Boolean = True) As String

            Dim sbQuery As New StringBuilder
            Dim shrtFlag As Integer

            With Value
                If Not isnothing(.CLIENTNAME) Then shrtFlag += VW_RPT_BIRTHDAY_LIST_VO.Enum_vw_rpt_birthday_list.CLIENTNAME
                If Year(.BIRTHDATE) > 1 Then shrtFlag += VW_RPT_BIRTHDAY_LIST_VO.Enum_vw_rpt_birthday_list.BIRTHDATE
                If Year(.BIRTHDATE_END) > 1 Then shrtFlag += VW_RPT_BIRTHDAY_LIST_VO.Enum_vw_rpt_birthday_list.BIRTHDATE_END
                If Year(.BIRTHDAY) > 1 Then shrtFlag += VW_RPT_BIRTHDAY_LIST_VO.Enum_vw_rpt_birthday_list.BIRTHDAY
                If Year(.BIRTHDAY_END) > 1 Then shrtFlag += VW_RPT_BIRTHDAY_LIST_VO.Enum_vw_rpt_birthday_list.BIRTHDAY_END
                If .AGE >= 0 Then shrtFlag += VW_RPT_BIRTHDAY_LIST_VO.Enum_vw_rpt_birthday_list.AGE
                If Not isnothing(.TYPE) Then shrtFlag += VW_RPT_BIRTHDAY_LIST_VO.Enum_vw_rpt_birthday_list.TYPE
                If Not isnothing(.ADVISOR) Then shrtFlag += VW_RPT_BIRTHDAY_LIST_VO.Enum_vw_rpt_birthday_list.ADVISOR
                If Not isnothing(.POLICY_NO) Then shrtFlag += VW_RPT_BIRTHDAY_LIST_VO.Enum_vw_rpt_birthday_list.POLICY_NO
                If Not isnothing(.PROVIDER_DESC) Then shrtFlag += VW_RPT_BIRTHDAY_LIST_VO.Enum_vw_rpt_birthday_list.PROVIDER_DESC
                If Not isnothing(.PRDT_DESC) Then shrtFlag += VW_RPT_BIRTHDAY_LIST_VO.Enum_vw_rpt_birthday_list.PRDT_DESC
                If Not isnothing(.ADVISOR_CD) Then shrtFlag += VW_RPT_BIRTHDAY_LIST_VO.Enum_vw_rpt_birthday_list.ADVISOR_CD
                If Not isnothing(.POLICY_SEQ_CD) Then shrtFlag += VW_RPT_BIRTHDAY_LIST_VO.Enum_vw_rpt_birthday_list.POLICY_SEQ_CD
                If Not isnothing(.CLIENT_CD) Then shrtFlag += VW_RPT_BIRTHDAY_LIST_VO.Enum_vw_rpt_birthday_list.CLIENT_CD
                If Not isnothing(.POLICY_OWNER) Then shrtFlag += VW_RPT_BIRTHDAY_LIST_VO.Enum_vw_rpt_birthday_list.POLICY_OWNER
                If Not isnothing(.TEL_NO) Then shrtFlag += VW_RPT_BIRTHDAY_LIST_VO.Enum_vw_rpt_birthday_list.TEL_NO
                If Not isnothing(.POLICY_ST) Then shrtFlag += VW_RPT_BIRTHDAY_LIST_VO.Enum_vw_rpt_birthday_list.POLICY_ST
                If .BIRTHMONTH >= 0 Then shrtFlag += VW_RPT_BIRTHDAY_LIST_VO.Enum_vw_rpt_birthday_list.BIRTHMONTH
            End With
            If shrtFlag <> 0 Then
                Dim bytCount As Byte

                If ExcludeFlag <> VW_RPT_BIRTHDAY_LIST_VO.Enum_vw_rpt_birthday_list.None Then shrtFlag -= ExcludeFlag
                With sbQuery

                    Select Case QueryType
                        Case QueryType.SelectType  ' Where
                            .Append("WHERE ")
                        Case QueryType.UpdateType  'Update
                            .Append("UPDATE vw_rpt_birthday_list SET ")
                        Case QueryType.InsertType  'Insert
                            .Append("INSERT INTO vw_rpt_birthday_list (")
                        Case QueryType.DeleteType   'Delete
                            .Append("DELETE FROM vw_rpt_birthday_list WHERE ")
                    End Select

                    Do While shrtFlag > 0
                        If CheckBit(shrtFlag, VW_RPT_BIRTHDAY_LIST_VO.Enum_vw_rpt_birthday_list.CLIENTNAME) Then
                            .Append("CLIENTNAME")
                            shrtFlag -= VW_RPT_BIRTHDAY_LIST_VO.Enum_vw_rpt_birthday_list.CLIENTNAME

                        ElseIf CheckBit(shrtFlag, VW_RPT_BIRTHDAY_LIST_VO.Enum_vw_rpt_birthday_list.birthdate) Then
                            If CheckBit(shrtFlag, VW_RPT_BIRTHDAY_LIST_VO.Enum_vw_rpt_birthday_list.birthdate_END) Then
                                shrtFlag -= VW_RPT_BIRTHDAY_LIST_VO.Enum_vw_rpt_birthday_list.birthdate
                                shrtFlag -= VW_RPT_BIRTHDAY_LIST_VO.Enum_vw_rpt_birthday_list.birthdate_END
                                .Append(" BIRTHDATE between ? And ? ")
                                QueryType = 4
                            End If
                        ElseIf CheckBit(shrtFlag, VW_RPT_BIRTHDAY_LIST_VO.Enum_vw_rpt_birthday_list.Birthday) Then
                            If CheckBit(shrtFlag, VW_RPT_BIRTHDAY_LIST_VO.Enum_vw_rpt_birthday_list.Birthday_END) Then
                                shrtFlag -= VW_RPT_BIRTHDAY_LIST_VO.Enum_vw_rpt_birthday_list.Birthday
                                shrtFlag -= VW_RPT_BIRTHDAY_LIST_VO.Enum_vw_rpt_birthday_list.Birthday_END
                                .Append(" BIRTHDAY between ? And ? ")
                                QueryType = 4
                            End If
                        ElseIf CheckBit(shrtFlag, VW_RPT_BIRTHDAY_LIST_VO.Enum_vw_rpt_birthday_list.AGE) Then
                            .Append("AGE")
                            shrtFlag -= VW_RPT_BIRTHDAY_LIST_VO.Enum_vw_rpt_birthday_list.AGE

                        ElseIf CheckBit(shrtFlag, VW_RPT_BIRTHDAY_LIST_VO.Enum_vw_rpt_birthday_list.TYPE) Then
                            .Append("TYPE")
                            shrtFlag -= VW_RPT_BIRTHDAY_LIST_VO.Enum_vw_rpt_birthday_list.TYPE

                        ElseIf CheckBit(shrtFlag, VW_RPT_BIRTHDAY_LIST_VO.Enum_vw_rpt_birthday_list.ADVISOR) Then
                            .Append("ADVISOR")
                            shrtFlag -= VW_RPT_BIRTHDAY_LIST_VO.Enum_vw_rpt_birthday_list.ADVISOR

                        ElseIf CheckBit(shrtFlag, VW_RPT_BIRTHDAY_LIST_VO.Enum_vw_rpt_birthday_list.POLICY_NO) Then
                            .Append("POLICY_NO")
                            shrtFlag -= VW_RPT_BIRTHDAY_LIST_VO.Enum_vw_rpt_birthday_list.POLICY_NO

                        ElseIf CheckBit(shrtFlag, VW_RPT_BIRTHDAY_LIST_VO.Enum_vw_rpt_birthday_list.PROVIDER_DESC) Then
                            .Append("PROVIDER_DESC")
                            shrtFlag -= VW_RPT_BIRTHDAY_LIST_VO.Enum_vw_rpt_birthday_list.PROVIDER_DESC

                        ElseIf CheckBit(shrtFlag, VW_RPT_BIRTHDAY_LIST_VO.Enum_vw_rpt_birthday_list.PRDT_DESC) Then
                            .Append("PRDT_DESC")
                            shrtFlag -= VW_RPT_BIRTHDAY_LIST_VO.Enum_vw_rpt_birthday_list.PRDT_DESC

                        ElseIf CheckBit(shrtFlag, VW_RPT_BIRTHDAY_LIST_VO.Enum_vw_rpt_birthday_list.ADVISOR_CD) Then
                            .Append("ADVISOR_CD")
                            shrtFlag -= VW_RPT_BIRTHDAY_LIST_VO.Enum_vw_rpt_birthday_list.ADVISOR_CD

                        ElseIf CheckBit(shrtFlag, VW_RPT_BIRTHDAY_LIST_VO.Enum_vw_rpt_birthday_list.POLICY_SEQ_CD) Then
                            .Append("POLICY_SEQ_CD")
                            shrtFlag -= VW_RPT_BIRTHDAY_LIST_VO.Enum_vw_rpt_birthday_list.POLICY_SEQ_CD

                        ElseIf CheckBit(shrtFlag, VW_RPT_BIRTHDAY_LIST_VO.Enum_vw_rpt_birthday_list.CLIENT_CD) Then
                            .Append("CLIENT_CD")
                            shrtFlag -= VW_RPT_BIRTHDAY_LIST_VO.Enum_vw_rpt_birthday_list.CLIENT_CD

                        ElseIf CheckBit(shrtFlag, VW_RPT_BIRTHDAY_LIST_VO.Enum_vw_rpt_birthday_list.POLICY_OWNER) Then
                            .Append("POLICY_OWNER")
                            shrtFlag -= VW_RPT_BIRTHDAY_LIST_VO.Enum_vw_rpt_birthday_list.POLICY_OWNER

                        ElseIf CheckBit(shrtFlag, VW_RPT_BIRTHDAY_LIST_VO.Enum_vw_rpt_birthday_list.TEL_NO) Then
                            .Append("TEL_NO")
                            shrtFlag -= VW_RPT_BIRTHDAY_LIST_VO.Enum_vw_rpt_birthday_list.TEL_NO

                        ElseIf CheckBit(shrtFlag, VW_RPT_BIRTHDAY_LIST_VO.Enum_vw_rpt_birthday_list.POLICY_ST) Then
                            .Append("POLICY_ST")
                            shrtFlag -= VW_RPT_BIRTHDAY_LIST_VO.Enum_vw_rpt_birthday_list.POLICY_ST

                        ElseIf CheckBit(shrtFlag, VW_RPT_BIRTHDAY_LIST_VO.Enum_vw_rpt_birthday_list.BIRTHMONTH) Then
                            .Append("BIRTHMONTH")
                            shrtFlag -= VW_RPT_BIRTHDAY_LIST_VO.Enum_vw_rpt_birthday_list.BIRTHMONTH

                        End If

                        Select Case QueryType
                            Case QueryType.SelectType, QueryType.DeleteType  ' Where Clause
                                Select Case SQLLike
                                    Case False
                                        .Append(" = ? AND ")

                                    Case True
                                        .Append(" LIKE ? AND ")

                                End Select

                            Case QueryType.UpdateType  ' Update
                                .Append(" = ?, ")

                            Case QueryType.InsertType  ' Insert
                                .Append(", ")
                                bytCount += 1

                            Case Else 'bypass
                                .Append(" AND ")
                                QueryType = 0

                        End Select
                    Loop

                    Select Case QueryType
                        Case QueryType.SelectType, QueryType.DeleteType  'Where Clause
                            .Replace(" AND ", "", .Length - 6, 6)
                            .Append(";")

                        Case QueryType.UpdateType   ' Update
                            .Replace("?, ", "? ", .Length - 3, 3)

                        Case QueryType.InsertType  ' Insert
                            .Replace(", ", ") VALUES (", .Length - 2, 2)
                            .Append("#", bytCount)
                            .Replace("#", "?,")
                            .Replace("?,", "?);", .Length - 2, 2)
                    End Select
                End With
            End If
            Return sbQuery.ToString
        End Function

        Private Sub ParamBuilder(ByRef Command As OLEDBCommand, ByVal Value As vw_rpt_birthday_list_VO, Optional ByVal QueryType As QueryType = 0, Optional ByVal ExcludeValue As vw_rpt_birthday_list_VO.Enum_vw_rpt_birthday_list = vw_rpt_birthday_list_VO.Enum_vw_rpt_birthday_list.None)
            With Command.Parameters
                If Not isnothing(Value.CLIENTNAME) And Not CheckBit(ExcludeValue, vw_rpt_birthday_list_VO.Enum_vw_rpt_birthday_list.CLIENTNAME) Then .AddWithValue("@ClientName", Value.CLIENTNAME)
                If Year(Value.BIRTHDATE) > 1 And Not CheckBit(ExcludeValue, vw_rpt_birthday_list_VO.Enum_vw_rpt_birthday_list.BIRTHDATE) Then .AddWithValue("@birthdate", Value.BIRTHDATE)
                If Year(Value.BIRTHDATE_END) > 1 And Not CheckBit(ExcludeValue, vw_rpt_birthday_list_VO.Enum_vw_rpt_birthday_list.BIRTHDATE_END) Then .AddWithValue("@birthdate_END", Value.BIRTHDATE_END)
                If Year(Value.BIRTHDAY) > 1 And Not CheckBit(ExcludeValue, vw_rpt_birthday_list_VO.Enum_vw_rpt_birthday_list.BIRTHDAY) Then .AddWithValue("@Birthday", Value.BIRTHDAY)
                If Year(Value.BIRTHDAY_END) > 1 And Not CheckBit(ExcludeValue, vw_rpt_birthday_list_VO.Enum_vw_rpt_birthday_list.BIRTHDAY_END) Then .AddWithValue("@Birthday_END", Value.BIRTHDAY_END)
                If Value.AGE >= 0 And Not CheckBit(ExcludeValue, vw_rpt_birthday_list_VO.Enum_vw_rpt_birthday_list.AGE) Then .AddWithValue("@Age", Value.AGE)
                If Not isnothing(Value.TYPE) And Not CheckBit(ExcludeValue, vw_rpt_birthday_list_VO.Enum_vw_rpt_birthday_list.TYPE) Then .AddWithValue("@Type", Value.TYPE)
                If Not isnothing(Value.ADVISOR) And Not CheckBit(ExcludeValue, vw_rpt_birthday_list_VO.Enum_vw_rpt_birthday_list.ADVISOR) Then .AddWithValue("@advisor", Value.ADVISOR)
                If Not isnothing(Value.POLICY_NO) And Not CheckBit(ExcludeValue, vw_rpt_birthday_list_VO.Enum_vw_rpt_birthday_list.POLICY_NO) Then .AddWithValue("@policy_no", Value.POLICY_NO)
                If Not isnothing(Value.PROVIDER_DESC) And Not CheckBit(ExcludeValue, vw_rpt_birthday_list_VO.Enum_vw_rpt_birthday_list.PROVIDER_DESC) Then .AddWithValue("@provider_desc", Value.PROVIDER_DESC)
                If Not isnothing(Value.PRDT_DESC) And Not CheckBit(ExcludeValue, vw_rpt_birthday_list_VO.Enum_vw_rpt_birthday_list.PRDT_DESC) Then .AddWithValue("@prdt_desc", Value.PRDT_DESC)
                If Not isnothing(Value.ADVISOR_CD) And Not CheckBit(ExcludeValue, vw_rpt_birthday_list_VO.Enum_vw_rpt_birthday_list.ADVISOR_CD) Then .AddWithValue("@Advisor_Cd", Value.ADVISOR_CD)
                If Not isnothing(Value.POLICY_SEQ_CD) And Not CheckBit(ExcludeValue, vw_rpt_birthday_list_VO.Enum_vw_rpt_birthday_list.POLICY_SEQ_CD) Then .AddWithValue("@policy_seq_cd", Value.POLICY_SEQ_CD)
                If Not isnothing(Value.CLIENT_CD) And Not CheckBit(ExcludeValue, vw_rpt_birthday_list_VO.Enum_vw_rpt_birthday_list.CLIENT_CD) Then .AddWithValue("@client_cd", Value.CLIENT_CD)
                If Not isnothing(Value.POLICY_OWNER) And Not CheckBit(ExcludeValue, vw_rpt_birthday_list_VO.Enum_vw_rpt_birthday_list.POLICY_OWNER) Then .AddWithValue("@policy_owner", Value.POLICY_OWNER)
                If Not isnothing(Value.TEL_NO) And Not CheckBit(ExcludeValue, vw_rpt_birthday_list_VO.Enum_vw_rpt_birthday_list.TEL_NO) Then .AddWithValue("@tel_no", Value.TEL_NO)
                If Not isnothing(Value.POLICY_ST) And Not CheckBit(ExcludeValue, vw_rpt_birthday_list_VO.Enum_vw_rpt_birthday_list.POLICY_ST) Then .AddWithValue("@Policy_St", Value.POLICY_ST)
                If Value.BIRTHMONTH >= 0 And Not CheckBit(ExcludeValue, vw_rpt_birthday_list_VO.Enum_vw_rpt_birthday_list.BIRTHMONTH) Then .AddWithValue("@birthmonth", Value.BIRTHMONTH)
            End With
        End Sub
        Public Function GetQuery(ByVal Value As vw_rpt_birthday_list_VO, Optional ByVal ExcludeValue As vw_rpt_birthday_list_VO.Enum_vw_rpt_birthday_list = 0, Optional ByVal SQLLike As Boolean = False) As String
            Dim strReturn As String = "SELECT * FROM vw_rpt_birthday_list " & QueryBuilder(Value, QueryType.SelectType, ExcludeValue, , SQLLike)
            Return strReturn
        End Function

    End Class

#End Region

#Region "vw_rpt_masterlist"

    <Serializable()> _
    Public Class VW_RPT_MASTERLIST_VO

        Public ADVISOR As String
        Public POLICY_NO As String
        Public CLIENT_NAME As String
        Public PROVIDER_DESC As String
        Public PRDT_DESC As String
        Public BROKER_CD As String
        Public INSURED_AMT As Double = -1
        Public CONTRIBUTION_AMT As Double = -1
        Public CURRENCY As String
        Public POLICY_STATUS As String
        Public AGE As Integer = -1
        Public AGEGROUP As String
        Public PREMIUM_RANGE As String
        Public INSURED_RANGE As String
        Public ADVISOR_CD As String

        Public Enum Enum_vw_rpt_masterlist As Integer
            NONE = 0
            Advisor = 1
            policy_no = 2
            Client_Name = 4
            provider_desc = 8
            prdt_desc = 16
            broker_cd = 32
            insured_amt = 64
            contribution_amt = 128
            currency = 256
            policy_status = 512
            Age = 1024
            AgeGroup = 2048
            Premium_range = 4096
            Insured_range = 8192
            Advisor_Cd = 16384
        End Enum

    End Class

    Public Class VW_RPT_MASTERLIST_DAO
        Inherits CommonDAO

        Public Function Search(ByVal Value As vw_rpt_masterlist_VO, Optional ByVal ExcludeValue As vw_rpt_masterlist_VO.Enum_vw_rpt_masterlist = 0, Optional ByVal SQLLike As Boolean = False) As Data.Datatable
            Dim daoMain As New Polydao.Common
            Dim cnnSearch As New OleDbConnection
            Dim dtSearch As New Data.DataTable

            Try
                daoMain.ConnectionString = Me.ConnectionString
                cnnSearch = daoMain.OpenConnection

                Dim cmdSearch As New OLEDBCommand

                With cmdSearch
                    .Connection = cnnSearch
                    .CommandText = "SELECT * FROM vw_rpt_masterlist " & QueryBuilder(Value, QueryType.SelectType, ExcludeValue, , SQLLike)
                End With

                Call ParamBuilder(cmdSearch, Value, ExcludeValue)

                Dim daSearch As New OleDbDataAdapter(cmdSearch)

                daSearch.Fill(dtSearch)

            Catch ex As Exception

                Debug.WriteLine(ex.Message)

            Finally
                daoMain.CloseConnection(cnnSearch)

            End Try

            Return dtSearch
        End Function

        Private Function QueryBuilder(ByVal Value As VW_RPT_MASTERLIST_VO, Optional ByVal QueryType As QueryType = 0, _
         Optional ByVal ExcludeFlag As VW_RPT_MASTERLIST_VO.Enum_vw_rpt_masterlist = VW_RPT_MASTERLIST_VO.Enum_vw_rpt_masterlist.NONE, Optional ByVal TableAlias As String = "", Optional ByVal SQLLike As Boolean = False, _
         Optional ByVal IncludeBlanks As Boolean = True) As String

            Dim sbQuery As New StringBuilder
            Dim shrtFlag As Integer

            With Value
                If Not isnothing(.ADVISOR) Then shrtFlag += VW_RPT_MASTERLIST_VO.Enum_vw_rpt_masterlist.ADVISOR
                If Not isnothing(.POLICY_NO) Then shrtFlag += VW_RPT_MASTERLIST_VO.Enum_vw_rpt_masterlist.POLICY_NO
                If Not isnothing(.CLIENT_NAME) Then shrtFlag += VW_RPT_MASTERLIST_VO.Enum_vw_rpt_masterlist.CLIENT_NAME
                If Not isnothing(.PROVIDER_DESC) Then shrtFlag += VW_RPT_MASTERLIST_VO.Enum_vw_rpt_masterlist.PROVIDER_DESC
                If Not isnothing(.PRDT_DESC) Then shrtFlag += VW_RPT_MASTERLIST_VO.Enum_vw_rpt_masterlist.PRDT_DESC
                If Not isnothing(.BROKER_CD) Then shrtFlag += VW_RPT_MASTERLIST_VO.Enum_vw_rpt_masterlist.BROKER_CD
                If .INSURED_AMT >= 0 Then shrtFlag += VW_RPT_MASTERLIST_VO.Enum_vw_rpt_masterlist.INSURED_AMT
                If .CONTRIBUTION_AMT >= 0 Then shrtFlag += VW_RPT_MASTERLIST_VO.Enum_vw_rpt_masterlist.CONTRIBUTION_AMT
                If Not isnothing(.CURRENCY) Then shrtFlag += VW_RPT_MASTERLIST_VO.Enum_vw_rpt_masterlist.CURRENCY
                If Not isnothing(.POLICY_STATUS) Then shrtFlag += VW_RPT_MASTERLIST_VO.Enum_vw_rpt_masterlist.POLICY_STATUS
                If .AGE >= 0 Then shrtFlag += VW_RPT_MASTERLIST_VO.Enum_vw_rpt_masterlist.AGE
                If Not isnothing(.AGEGROUP) Then shrtFlag += VW_RPT_MASTERLIST_VO.Enum_vw_rpt_masterlist.AGEGROUP
                If Not isnothing(.PREMIUM_RANGE) Then shrtFlag += VW_RPT_MASTERLIST_VO.Enum_vw_rpt_masterlist.PREMIUM_RANGE
                If Not isnothing(.INSURED_RANGE) Then shrtFlag += VW_RPT_MASTERLIST_VO.Enum_vw_rpt_masterlist.INSURED_RANGE
                If Not isnothing(.ADVISOR_CD) Then shrtFlag += VW_RPT_MASTERLIST_VO.Enum_vw_rpt_masterlist.ADVISOR_CD
            End With
            If shrtFlag <> 0 Then
                Dim bytCount As Byte

                If ExcludeFlag <> VW_RPT_MASTERLIST_VO.Enum_vw_rpt_masterlist.None Then shrtFlag -= ExcludeFlag
                With sbQuery

                    Select Case QueryType
                        Case QueryType.SelectType  ' Where
                            .Append("WHERE ")
                        Case QueryType.UpdateType  'Update
                            .Append("UPDATE vw_rpt_masterlist SET ")
                        Case QueryType.InsertType  'Insert
                            .Append("INSERT INTO vw_rpt_masterlist (")
                        Case QueryType.DeleteType   'Delete
                            .Append("DELETE FROM vw_rpt_masterlist WHERE ")
                    End Select

                    Do While shrtFlag > 0
                        If CheckBit(shrtFlag, VW_RPT_MASTERLIST_VO.Enum_vw_rpt_masterlist.ADVISOR) Then
                            .Append("ADVISOR")
                            shrtFlag -= VW_RPT_MASTERLIST_VO.Enum_vw_rpt_masterlist.ADVISOR

                        ElseIf CheckBit(shrtFlag, VW_RPT_MASTERLIST_VO.Enum_vw_rpt_masterlist.POLICY_NO) Then
                            .Append("POLICY_NO")
                            shrtFlag -= VW_RPT_MASTERLIST_VO.Enum_vw_rpt_masterlist.POLICY_NO

                        ElseIf CheckBit(shrtFlag, VW_RPT_MASTERLIST_VO.Enum_vw_rpt_masterlist.CLIENT_NAME) Then
                            .Append("CLIENT_NAME")
                            shrtFlag -= VW_RPT_MASTERLIST_VO.Enum_vw_rpt_masterlist.CLIENT_NAME

                        ElseIf CheckBit(shrtFlag, VW_RPT_MASTERLIST_VO.Enum_vw_rpt_masterlist.PROVIDER_DESC) Then
                            .Append("PROVIDER_DESC")
                            shrtFlag -= VW_RPT_MASTERLIST_VO.Enum_vw_rpt_masterlist.PROVIDER_DESC

                        ElseIf CheckBit(shrtFlag, VW_RPT_MASTERLIST_VO.Enum_vw_rpt_masterlist.PRDT_DESC) Then
                            .Append("PRDT_DESC")
                            shrtFlag -= VW_RPT_MASTERLIST_VO.Enum_vw_rpt_masterlist.PRDT_DESC

                        ElseIf CheckBit(shrtFlag, VW_RPT_MASTERLIST_VO.Enum_vw_rpt_masterlist.BROKER_CD) Then
                            .Append("BROKER_CD")
                            shrtFlag -= VW_RPT_MASTERLIST_VO.Enum_vw_rpt_masterlist.BROKER_CD

                        ElseIf CheckBit(shrtFlag, VW_RPT_MASTERLIST_VO.Enum_vw_rpt_masterlist.INSURED_AMT) Then
                            .Append("INSURED_AMT")
                            shrtFlag -= VW_RPT_MASTERLIST_VO.Enum_vw_rpt_masterlist.INSURED_AMT

                        ElseIf CheckBit(shrtFlag, VW_RPT_MASTERLIST_VO.Enum_vw_rpt_masterlist.CONTRIBUTION_AMT) Then
                            .Append("CONTRIBUTION_AMT")
                            shrtFlag -= VW_RPT_MASTERLIST_VO.Enum_vw_rpt_masterlist.CONTRIBUTION_AMT

                        ElseIf CheckBit(shrtFlag, VW_RPT_MASTERLIST_VO.Enum_vw_rpt_masterlist.CURRENCY) Then
                            .Append("CURRENCY")
                            shrtFlag -= VW_RPT_MASTERLIST_VO.Enum_vw_rpt_masterlist.CURRENCY

                        ElseIf CheckBit(shrtFlag, VW_RPT_MASTERLIST_VO.Enum_vw_rpt_masterlist.POLICY_STATUS) Then
                            .Append("POLICY_STATUS")
                            shrtFlag -= VW_RPT_MASTERLIST_VO.Enum_vw_rpt_masterlist.POLICY_STATUS

                        ElseIf CheckBit(shrtFlag, VW_RPT_MASTERLIST_VO.Enum_vw_rpt_masterlist.AGE) Then
                            .Append("AGE")
                            shrtFlag -= VW_RPT_MASTERLIST_VO.Enum_vw_rpt_masterlist.AGE

                        ElseIf CheckBit(shrtFlag, VW_RPT_MASTERLIST_VO.Enum_vw_rpt_masterlist.AGEGROUP) Then
                            .Append("AGEGROUP")
                            shrtFlag -= VW_RPT_MASTERLIST_VO.Enum_vw_rpt_masterlist.AGEGROUP

                        ElseIf CheckBit(shrtFlag, VW_RPT_MASTERLIST_VO.Enum_vw_rpt_masterlist.PREMIUM_RANGE) Then
                            .Append("PREMIUM_RANGE")
                            shrtFlag -= VW_RPT_MASTERLIST_VO.Enum_vw_rpt_masterlist.PREMIUM_RANGE

                        ElseIf CheckBit(shrtFlag, VW_RPT_MASTERLIST_VO.Enum_vw_rpt_masterlist.INSURED_RANGE) Then
                            .Append("INSURED_RANGE")
                            shrtFlag -= VW_RPT_MASTERLIST_VO.Enum_vw_rpt_masterlist.INSURED_RANGE

                        ElseIf CheckBit(shrtFlag, VW_RPT_MASTERLIST_VO.Enum_vw_rpt_masterlist.ADVISOR_CD) Then
                            .Append("ADVISOR_CD")
                            shrtFlag -= VW_RPT_MASTERLIST_VO.Enum_vw_rpt_masterlist.ADVISOR_CD

                        End If

                        Select Case QueryType
                            Case QueryType.SelectType, QueryType.DeleteType  ' Where Clause
                                Select Case SQLLike
                                    Case False
                                        .Append(" = ? AND ")

                                    Case True
                                        .Append(" LIKE ? AND ")

                                End Select

                            Case QueryType.UpdateType  ' Update
                                .Append(" = ?, ")

                            Case QueryType.InsertType  ' Insert
                                .Append(", ")
                                bytCount += 1

                            Case Else 'bypass
                                .Append(" AND ")
                                QueryType = 0

                        End Select
                    Loop

                    Select Case QueryType
                        Case QueryType.SelectType, QueryType.DeleteType  'Where Clause
                            .Replace(" AND ", "", .Length - 6, 6)
                            .Append(";")

                        Case QueryType.UpdateType   ' Update
                            .Replace("?, ", "? ", .Length - 3, 3)

                        Case QueryType.InsertType  ' Insert
                            .Replace(", ", ") VALUES (", .Length - 2, 2)
                            .Append("#", bytCount)
                            .Replace("#", "?,")
                            .Replace("?,", "?);", .Length - 2, 2)
                    End Select
                End With
            End If
            Return sbQuery.ToString
        End Function

        Private Sub ParamBuilder(ByRef Command As OLEDBCommand, ByVal Value As vw_rpt_masterlist_VO, Optional ByVal QueryType As QueryType = 0, Optional ByVal ExcludeValue As vw_rpt_masterlist_VO.Enum_vw_rpt_masterlist = vw_rpt_masterlist_VO.Enum_vw_rpt_masterlist.None)
            With Command.Parameters
                If Not isnothing(Value.ADVISOR) And Not CheckBit(ExcludeValue, vw_rpt_masterlist_VO.Enum_vw_rpt_masterlist.ADVISOR) Then .AddWithValue("@Advisor", Value.ADVISOR)
                If Not isnothing(Value.POLICY_NO) And Not CheckBit(ExcludeValue, vw_rpt_masterlist_VO.Enum_vw_rpt_masterlist.POLICY_NO) Then .AddWithValue("@policy_no", Value.POLICY_NO)
                If Not isnothing(Value.CLIENT_NAME) And Not CheckBit(ExcludeValue, vw_rpt_masterlist_VO.Enum_vw_rpt_masterlist.CLIENT_NAME) Then .AddWithValue("@Client_Name", Value.CLIENT_NAME)
                If Not isnothing(Value.PROVIDER_DESC) And Not CheckBit(ExcludeValue, vw_rpt_masterlist_VO.Enum_vw_rpt_masterlist.PROVIDER_DESC) Then .AddWithValue("@provider_desc", Value.PROVIDER_DESC)
                If Not isnothing(Value.PRDT_DESC) And Not CheckBit(ExcludeValue, vw_rpt_masterlist_VO.Enum_vw_rpt_masterlist.PRDT_DESC) Then .AddWithValue("@prdt_desc", Value.PRDT_DESC)
                If Not isnothing(Value.BROKER_CD) And Not CheckBit(ExcludeValue, vw_rpt_masterlist_VO.Enum_vw_rpt_masterlist.BROKER_CD) Then .AddWithValue("@broker_cd", Value.BROKER_CD)
                If Value.INSURED_AMT >= 0 And Not CheckBit(ExcludeValue, vw_rpt_masterlist_VO.Enum_vw_rpt_masterlist.INSURED_AMT) Then .AddWithValue("@insured_amt", Value.INSURED_AMT)
                If Value.CONTRIBUTION_AMT >= 0 And Not CheckBit(ExcludeValue, vw_rpt_masterlist_VO.Enum_vw_rpt_masterlist.CONTRIBUTION_AMT) Then .AddWithValue("@contribution_amt", Value.CONTRIBUTION_AMT)
                If Not isnothing(Value.CURRENCY) And Not CheckBit(ExcludeValue, vw_rpt_masterlist_VO.Enum_vw_rpt_masterlist.CURRENCY) Then .AddWithValue("@currency", Value.CURRENCY)
                If Not isnothing(Value.POLICY_STATUS) And Not CheckBit(ExcludeValue, vw_rpt_masterlist_VO.Enum_vw_rpt_masterlist.POLICY_STATUS) Then .AddWithValue("@policy_status", Value.POLICY_STATUS)
                If Value.AGE >= 0 And Not CheckBit(ExcludeValue, vw_rpt_masterlist_VO.Enum_vw_rpt_masterlist.AGE) Then .AddWithValue("@Age", Value.AGE)
                If Not isnothing(Value.AGEGROUP) And Not CheckBit(ExcludeValue, vw_rpt_masterlist_VO.Enum_vw_rpt_masterlist.AGEGROUP) Then .AddWithValue("@AgeGroup", Value.AGEGROUP)
                If Not isnothing(Value.PREMIUM_RANGE) And Not CheckBit(ExcludeValue, vw_rpt_masterlist_VO.Enum_vw_rpt_masterlist.PREMIUM_RANGE) Then .AddWithValue("@Premium_range", Value.PREMIUM_RANGE)
                If Not isnothing(Value.INSURED_RANGE) And Not CheckBit(ExcludeValue, vw_rpt_masterlist_VO.Enum_vw_rpt_masterlist.INSURED_RANGE) Then .AddWithValue("@Insured_range", Value.INSURED_RANGE)
                If Not isnothing(Value.ADVISOR_CD) And Not CheckBit(ExcludeValue, vw_rpt_masterlist_VO.Enum_vw_rpt_masterlist.ADVISOR_CD) Then .AddWithValue("@Advisor_Cd", Value.ADVISOR_CD)
            End With
        End Sub
        Public Function GetQuery(ByVal Value As vw_rpt_masterlist_VO, Optional ByVal ExcludeValue As vw_rpt_masterlist_VO.Enum_vw_rpt_masterlist = 0, Optional ByVal SQLLike As Boolean = False) As String
            Dim strReturn As String = "SELECT * FROM vw_rpt_masterlist " & QueryBuilder(Value, QueryType.SelectType, ExcludeValue, , SQLLike)
            Return strReturn
        End Function

    End Class

#End Region

#Region "vw_rpt_policy_submit_dtl"

    <Serializable()> _
    Public Class VW_RPT_POLICY_SUBMIT_DTL_VO

        Public POLICY_SEQ_CD As String
        Public POLICY_SUBMIT_DT As Date
        Public POLICY_NO As String
        Public NAME As String
        Public PROVIDER_DESC As String
        Public PRDT_DESC As String
        Public TEL_NO As String
        Public MOBILE_NO As String
        Public FAX_NO As String
        Public EMAIL_ADDRESS As String
        Public ADVISOR_CD As String
        Public ADVISOR As String
        Public STATUS As String
        Public END_DT As Date

        Public Enum Enum_VW_RPT_POLICY_SUBMIT_DTL As Short
            NONE = 0
            policy_seq_cd = 1
            policy_submit_dt = 2
            policy_no = 4
            Name = 8
            provider_desc = 16
            prdt_desc = 32
            tel_no = 64
            mobile_no = 128
            fax_no = 256
            email_address = 512
            Advisor_Cd = 1024
            Advisor = 2048
            Status = 4096
            End_DT = 8192
        End Enum

    End Class

    Public Class VW_RPT_POLICY_SUBMIT_DTL_DAO
        Inherits CommonDAO

        Public Function Search(ByVal Value As VW_RPT_POLICY_SUBMIT_DTL_VO, Optional ByVal ExcludeValue As VW_RPT_POLICY_SUBMIT_DTL_VO.Enum_VW_RPT_POLICY_SUBMIT_DTL = 0, Optional ByVal SQLLike As Boolean = False) As Data.DataTable
            Dim daoMain As New Polydao.Common
            Dim cnnSearch As New OleDbConnection
            Dim dtSearch As New Data.DataTable

            Try
                daoMain.ConnectionString = Me.ConnectionString
                cnnSearch = daoMain.OpenConnection

                Dim cmdSearch As New OleDbCommand

                With cmdSearch
                    .Connection = cnnSearch
                    .CommandText = "SELECT * FROM vw_rpt_policy_submit_dtl " & QueryBuilder(Value, QueryType.SelectType, ExcludeValue, , SQLLike)
                End With

                Call ParamBuilder(cmdSearch, Value, ExcludeValue)

                Dim daSearch As New OleDbDataAdapter(cmdSearch)

                daSearch.Fill(dtSearch)

            Catch ex As Exception

                Debug.WriteLine(ex.Message)

            Finally
                daoMain.CloseConnection(cnnSearch)

            End Try

            Return dtSearch
        End Function

        Private Function QueryBuilder(ByVal Value As VW_RPT_POLICY_SUBMIT_DTL_VO, Optional ByVal QueryType As QueryType = 0, _
         Optional ByVal ExcludeFlag As VW_RPT_POLICY_SUBMIT_DTL_VO.Enum_VW_RPT_POLICY_SUBMIT_DTL = VW_RPT_POLICY_SUBMIT_DTL_VO.Enum_VW_RPT_POLICY_SUBMIT_DTL.NONE, Optional ByVal TableAlias As String = "", Optional ByVal SQLLike As Boolean = False, _
         Optional ByVal IncludeBlanks As Boolean = True) As String

            Dim sbQuery As New StringBuilder
            Dim shrtFlag As Short

            With Value
                If Not IsNothing(.POLICY_SEQ_CD) Then shrtFlag += VW_RPT_POLICY_SUBMIT_DTL_VO.Enum_VW_RPT_POLICY_SUBMIT_DTL.policy_seq_cd
                If Year(.POLICY_SUBMIT_DT) > 1 Then shrtFlag += VW_RPT_POLICY_SUBMIT_DTL_VO.Enum_VW_RPT_POLICY_SUBMIT_DTL.policy_submit_dt
                If Not IsNothing(.POLICY_NO) Then shrtFlag += VW_RPT_POLICY_SUBMIT_DTL_VO.Enum_VW_RPT_POLICY_SUBMIT_DTL.policy_no
                If Not IsNothing(.NAME) Then shrtFlag += VW_RPT_POLICY_SUBMIT_DTL_VO.Enum_VW_RPT_POLICY_SUBMIT_DTL.Name
                If Not IsNothing(.PROVIDER_DESC) Then shrtFlag += VW_RPT_POLICY_SUBMIT_DTL_VO.Enum_VW_RPT_POLICY_SUBMIT_DTL.provider_desc
                If Not IsNothing(.PRDT_DESC) Then shrtFlag += VW_RPT_POLICY_SUBMIT_DTL_VO.Enum_VW_RPT_POLICY_SUBMIT_DTL.prdt_desc
                If Not IsNothing(.TEL_NO) Then shrtFlag += VW_RPT_POLICY_SUBMIT_DTL_VO.Enum_VW_RPT_POLICY_SUBMIT_DTL.tel_no
                If Not IsNothing(.MOBILE_NO) Then shrtFlag += VW_RPT_POLICY_SUBMIT_DTL_VO.Enum_VW_RPT_POLICY_SUBMIT_DTL.mobile_no
                If Not IsNothing(.FAX_NO) Then shrtFlag += VW_RPT_POLICY_SUBMIT_DTL_VO.Enum_VW_RPT_POLICY_SUBMIT_DTL.fax_no
                If Not IsNothing(.EMAIL_ADDRESS) Then shrtFlag += VW_RPT_POLICY_SUBMIT_DTL_VO.Enum_VW_RPT_POLICY_SUBMIT_DTL.email_address
                If Not IsNothing(.ADVISOR_CD) Then shrtFlag += VW_RPT_POLICY_SUBMIT_DTL_VO.Enum_VW_RPT_POLICY_SUBMIT_DTL.Advisor_Cd
                If Not IsNothing(.ADVISOR) Then shrtFlag += VW_RPT_POLICY_SUBMIT_DTL_VO.Enum_VW_RPT_POLICY_SUBMIT_DTL.Advisor
                If Not IsNothing(.STATUS) Then shrtFlag += VW_RPT_POLICY_SUBMIT_DTL_VO.Enum_VW_RPT_POLICY_SUBMIT_DTL.Status
                If Year(.END_DT) > 1 Then shrtFlag += VW_RPT_POLICY_SUBMIT_DTL_VO.Enum_VW_RPT_POLICY_SUBMIT_DTL.End_DT
            End With
            If shrtFlag <> 0 Then
                Dim bytCount As Byte

                If ExcludeFlag <> VW_RPT_POLICY_SUBMIT_DTL_VO.Enum_VW_RPT_POLICY_SUBMIT_DTL.NONE Then shrtFlag -= ExcludeFlag
                With sbQuery

                    Select Case QueryType
                        Case QueryType.SelectType  ' Where
                            .Append("WHERE ")
                        Case QueryType.UpdateType  'Update
                            .Append("UPDATE vw_rpt_policy_submit_dtl SET ")
                        Case QueryType.InsertType  'Insert
                            .Append("INSERT INTO vw_rpt_policy_submit_dtl (")
                        Case QueryType.DeleteType   'Delete
                            .Append("DELETE FROM vw_rpt_policy_submit_dtl WHERE ")
                    End Select

                    Do While shrtFlag > 0
                        If CheckBit(shrtFlag, VW_RPT_POLICY_SUBMIT_DTL_VO.Enum_VW_RPT_POLICY_SUBMIT_DTL.policy_seq_cd) Then
                            .Append("POLICY_SEQ_CD")
                            shrtFlag -= VW_RPT_POLICY_SUBMIT_DTL_VO.Enum_VW_RPT_POLICY_SUBMIT_DTL.policy_seq_cd

                        ElseIf CheckBit(shrtFlag, VW_RPT_POLICY_SUBMIT_DTL_VO.Enum_VW_RPT_POLICY_SUBMIT_DTL.policy_submit_dt) Then
                            If CheckBit(shrtFlag, VW_RPT_POLICY_SUBMIT_DTL_VO.Enum_VW_RPT_POLICY_SUBMIT_DTL.End_DT) And QueryType = 0 Then
                                .Append("POLICY_SUBMIT_DT BETWEEN ? AND ? ")
                                shrtFlag -= VW_RPT_POLICY_SUBMIT_DTL_VO.Enum_VW_RPT_POLICY_SUBMIT_DTL.policy_submit_dt
                                shrtFlag -= VW_RPT_POLICY_SUBMIT_DTL_VO.Enum_VW_RPT_POLICY_SUBMIT_DTL.End_DT
                                QueryType = 4
                            Else
                                .Append("POLICY_SUBMIT_DT")
                                shrtFlag -= VW_RPT_POLICY_SUBMIT_DTL_VO.Enum_VW_RPT_POLICY_SUBMIT_DTL.policy_submit_dt
                            End If

                        ElseIf CheckBit(shrtFlag, VW_RPT_POLICY_SUBMIT_DTL_VO.Enum_VW_RPT_POLICY_SUBMIT_DTL.policy_no) Then
                            .Append("POLICY_NO")
                            shrtFlag -= VW_RPT_POLICY_SUBMIT_DTL_VO.Enum_VW_RPT_POLICY_SUBMIT_DTL.policy_no

                        ElseIf CheckBit(shrtFlag, VW_RPT_POLICY_SUBMIT_DTL_VO.Enum_VW_RPT_POLICY_SUBMIT_DTL.Name) Then
                            .Append("NAME")
                            shrtFlag -= VW_RPT_POLICY_SUBMIT_DTL_VO.Enum_VW_RPT_POLICY_SUBMIT_DTL.Name

                        ElseIf CheckBit(shrtFlag, VW_RPT_POLICY_SUBMIT_DTL_VO.Enum_VW_RPT_POLICY_SUBMIT_DTL.provider_desc) Then
                            .Append("PROVIDER_DESC")
                            shrtFlag -= VW_RPT_POLICY_SUBMIT_DTL_VO.Enum_VW_RPT_POLICY_SUBMIT_DTL.provider_desc

                        ElseIf CheckBit(shrtFlag, VW_RPT_POLICY_SUBMIT_DTL_VO.Enum_VW_RPT_POLICY_SUBMIT_DTL.prdt_desc) Then
                            .Append("PRDT_DESC")
                            shrtFlag -= VW_RPT_POLICY_SUBMIT_DTL_VO.Enum_VW_RPT_POLICY_SUBMIT_DTL.prdt_desc

                        ElseIf CheckBit(shrtFlag, VW_RPT_POLICY_SUBMIT_DTL_VO.Enum_VW_RPT_POLICY_SUBMIT_DTL.tel_no) Then
                            .Append("TEL_NO")
                            shrtFlag -= VW_RPT_POLICY_SUBMIT_DTL_VO.Enum_VW_RPT_POLICY_SUBMIT_DTL.tel_no

                        ElseIf CheckBit(shrtFlag, VW_RPT_POLICY_SUBMIT_DTL_VO.Enum_VW_RPT_POLICY_SUBMIT_DTL.mobile_no) Then
                            .Append("MOBILE_NO")
                            shrtFlag -= VW_RPT_POLICY_SUBMIT_DTL_VO.Enum_VW_RPT_POLICY_SUBMIT_DTL.mobile_no

                        ElseIf CheckBit(shrtFlag, VW_RPT_POLICY_SUBMIT_DTL_VO.Enum_VW_RPT_POLICY_SUBMIT_DTL.fax_no) Then
                            .Append("FAX_NO")
                            shrtFlag -= VW_RPT_POLICY_SUBMIT_DTL_VO.Enum_VW_RPT_POLICY_SUBMIT_DTL.fax_no

                        ElseIf CheckBit(shrtFlag, VW_RPT_POLICY_SUBMIT_DTL_VO.Enum_VW_RPT_POLICY_SUBMIT_DTL.email_address) Then
                            .Append("EMAIL_ADDRESS")
                            shrtFlag -= VW_RPT_POLICY_SUBMIT_DTL_VO.Enum_VW_RPT_POLICY_SUBMIT_DTL.email_address

                        ElseIf CheckBit(shrtFlag, VW_RPT_POLICY_SUBMIT_DTL_VO.Enum_VW_RPT_POLICY_SUBMIT_DTL.Advisor_Cd) Then
                            .Append("ADVISOR_CD")
                            shrtFlag -= VW_RPT_POLICY_SUBMIT_DTL_VO.Enum_VW_RPT_POLICY_SUBMIT_DTL.Advisor_Cd

                        ElseIf CheckBit(shrtFlag, VW_RPT_POLICY_SUBMIT_DTL_VO.Enum_VW_RPT_POLICY_SUBMIT_DTL.Advisor) Then
                            .Append("ADVISOR")
                            shrtFlag -= VW_RPT_POLICY_SUBMIT_DTL_VO.Enum_VW_RPT_POLICY_SUBMIT_DTL.Advisor

                        ElseIf CheckBit(shrtFlag, VW_RPT_POLICY_SUBMIT_DTL_VO.Enum_VW_RPT_POLICY_SUBMIT_DTL.Status) Then
                            .Append("STATUS")
                            shrtFlag -= VW_RPT_POLICY_SUBMIT_DTL_VO.Enum_VW_RPT_POLICY_SUBMIT_DTL.Status

                        End If

                        Select Case QueryType
                            Case QueryType.SelectType, QueryType.DeleteType  ' Where Clause
                                Select Case SQLLike
                                    Case False
                                        .Append(" = ? AND ")

                                    Case True
                                        .Append(" LIKE ? AND ")

                                End Select

                            Case QueryType.UpdateType  ' Update
                                .Append(" = ?, ")

                            Case QueryType.InsertType  ' Insert
                                .Append(", ")
                                bytCount += 1

                            Case Else 'bypass
                                .Append(" AND ")
                                QueryType = 0

                        End Select
                    Loop

                    Select Case QueryType
                        Case QueryType.SelectType, QueryType.DeleteType  'Where Clause
                            .Replace(" AND ", "", .Length - 6, 6)
                            .Append(";")

                        Case QueryType.UpdateType   ' Update
                            .Replace("?, ", "? ", .Length - 3, 3)

                        Case QueryType.InsertType  ' Insert
                            .Replace(", ", ") VALUES (", .Length - 2, 2)
                            .Append("#", bytCount)
                            .Replace("#", "?,")
                            .Replace("?,", "?);", .Length - 2, 2)
                    End Select
                End With
            End If
            Return sbQuery.ToString
        End Function

        Private Sub ParamBuilder(ByRef Command As OleDbCommand, ByVal Value As VW_RPT_POLICY_SUBMIT_DTL_VO, Optional ByVal QueryType As QueryType = 0, Optional ByVal ExcludeValue As VW_RPT_POLICY_SUBMIT_DTL_VO.Enum_VW_RPT_POLICY_SUBMIT_DTL = VW_RPT_POLICY_SUBMIT_DTL_VO.Enum_VW_RPT_POLICY_SUBMIT_DTL.NONE)
            With Command.Parameters
                If Not IsNothing(Value.POLICY_SEQ_CD) And Not CheckBit(ExcludeValue, VW_RPT_POLICY_SUBMIT_DTL_VO.Enum_VW_RPT_POLICY_SUBMIT_DTL.policy_seq_cd) Then .AddWithValue("@policy_seq_cd", Value.POLICY_SEQ_CD)
                If Year(Value.POLICY_SUBMIT_DT) > 1 And Not CheckBit(ExcludeValue, VW_RPT_POLICY_SUBMIT_DTL_VO.Enum_VW_RPT_POLICY_SUBMIT_DTL.policy_submit_dt) Then .AddWithValue("@policy_submit_dt", Value.POLICY_SUBMIT_DT)
                If Year(Value.END_DT) > 1 And Not CheckBit(ExcludeValue, VW_RPT_POLICY_SUBMIT_DTL_VO.Enum_VW_RPT_POLICY_SUBMIT_DTL.End_DT) Then .AddWithValue("@End_DT", Value.END_DT)
                If Not IsNothing(Value.POLICY_NO) And Not CheckBit(ExcludeValue, VW_RPT_POLICY_SUBMIT_DTL_VO.Enum_VW_RPT_POLICY_SUBMIT_DTL.policy_no) Then .AddWithValue("@policy_no", Value.POLICY_NO)
                If Not IsNothing(Value.NAME) And Not CheckBit(ExcludeValue, VW_RPT_POLICY_SUBMIT_DTL_VO.Enum_VW_RPT_POLICY_SUBMIT_DTL.Name) Then .AddWithValue("@Name", Value.NAME)
                If Not IsNothing(Value.PROVIDER_DESC) And Not CheckBit(ExcludeValue, VW_RPT_POLICY_SUBMIT_DTL_VO.Enum_VW_RPT_POLICY_SUBMIT_DTL.provider_desc) Then .AddWithValue("@provider_desc", Value.PROVIDER_DESC)
                If Not IsNothing(Value.PRDT_DESC) And Not CheckBit(ExcludeValue, VW_RPT_POLICY_SUBMIT_DTL_VO.Enum_VW_RPT_POLICY_SUBMIT_DTL.prdt_desc) Then .AddWithValue("@prdt_desc", Value.PRDT_DESC)
                If Not IsNothing(Value.TEL_NO) And Not CheckBit(ExcludeValue, VW_RPT_POLICY_SUBMIT_DTL_VO.Enum_VW_RPT_POLICY_SUBMIT_DTL.tel_no) Then .AddWithValue("@tel_no", Value.TEL_NO)
                If Not IsNothing(Value.MOBILE_NO) And Not CheckBit(ExcludeValue, VW_RPT_POLICY_SUBMIT_DTL_VO.Enum_VW_RPT_POLICY_SUBMIT_DTL.mobile_no) Then .AddWithValue("@mobile_no", Value.MOBILE_NO)
                If Not IsNothing(Value.FAX_NO) And Not CheckBit(ExcludeValue, VW_RPT_POLICY_SUBMIT_DTL_VO.Enum_VW_RPT_POLICY_SUBMIT_DTL.fax_no) Then .AddWithValue("@fax_no", Value.FAX_NO)
                If Not IsNothing(Value.EMAIL_ADDRESS) And Not CheckBit(ExcludeValue, VW_RPT_POLICY_SUBMIT_DTL_VO.Enum_VW_RPT_POLICY_SUBMIT_DTL.email_address) Then .AddWithValue("@email_address", Value.EMAIL_ADDRESS)
                If Not IsNothing(Value.ADVISOR_CD) And Not CheckBit(ExcludeValue, VW_RPT_POLICY_SUBMIT_DTL_VO.Enum_VW_RPT_POLICY_SUBMIT_DTL.Advisor_Cd) Then .AddWithValue("@Advisor_Cd", Value.ADVISOR_CD)
                If Not IsNothing(Value.ADVISOR) And Not CheckBit(ExcludeValue, VW_RPT_POLICY_SUBMIT_DTL_VO.Enum_VW_RPT_POLICY_SUBMIT_DTL.Advisor) Then .AddWithValue("@Advisor", Value.ADVISOR)
                If Not IsNothing(Value.STATUS) And Not CheckBit(ExcludeValue, VW_RPT_POLICY_SUBMIT_DTL_VO.Enum_VW_RPT_POLICY_SUBMIT_DTL.Status) Then .AddWithValue("@Status", Value.STATUS)
            End With
        End Sub

        Public Function GetQuery(ByVal Value As VW_RPT_POLICY_SUBMIT_DTL_VO, Optional ByVal ExcludeValue As VW_RPT_POLICY_SUBMIT_DTL_VO.Enum_VW_RPT_POLICY_SUBMIT_DTL = 0, Optional ByVal SQLLike As Boolean = False) As String
            Dim strReturn As String = "SELECT * FROM vw_rpt_policy_submit_dtl " & QueryBuilder(Value, QueryType.SelectType, ExcludeValue, , SQLLike)
            Return strReturn
        End Function

    End Class

#End Region

#Region "vw_rpt_monthly_renewal"

    <Serializable()> _
    Public Class VW_RPT_MONTHLY_RENEWAL_VO

        Public CLIENT_NAME As String
        Public POLICY_NO As String
        Public PROVIDER_DESC As String
        Public PRDT_DESC As String
        Public POLICY_DUE_DT As Date
        Public POLICY_DUE_DT_END As Date
        Public CONTRIBUTION_AMT As Double = -1
        Public CURRENCY_DESC As String
        Public ADVISOR_CD As String
        Public FREQUENCY_DESC As String
        Public POLICY_STATUS As String

        Public Enum Enum_VW_RPT_MONTHLY_RENEWAL As Short
            NONE = 0
            Client_Name = 1
            policy_no = 2
            provider_desc = 4
            prdt_desc = 8
            policy_due_dt = 16
            policy_due_dt_END = 32
            contribution_amt = 64
            currency_desc = 128
            Advisor_Cd = 256
            frequency_desc = 512
            policy_status = 1024
        End Enum

    End Class

    Public Class VW_RPT_MONTHLY_RENEWAL_DAO
        Inherits DAOMain.CommonDAO

        Public Function Search(ByVal Value As vw_rpt_monthly_renewal_VO, Optional ByVal ExcludeValue As vw_rpt_monthly_renewal_VO.Enum_vw_rpt_monthly_renewal = 0, Optional ByVal SQLLike As Boolean = False) As Data.Datatable
            Dim daoMain As New Polydao.Common
            Dim cnnSearch As New OleDbConnection
            Dim dtSearch As New Data.DataTable

            Try
                daoMain.ConnectionString = Me.ConnectionString
                cnnSearch = daoMain.OpenConnection

                Dim cmdSearch As New OLEDBCommand

                With cmdSearch
                    .Connection = cnnSearch
                    .CommandText = "SELECT * FROM vw_rpt_monthly_renewal " & QueryBuilder(Value, QueryType.SelectType, ExcludeValue, , SQLLike) & " Order by Advisor_Cd, prdt_desc, Client_Name, policy_due_dt"
                End With

                Call ParamBuilder(cmdSearch, Value, ExcludeValue)

                Dim daSearch As New OleDbDataAdapter(cmdSearch)

                daSearch.Fill(dtSearch)

            Catch ex As Exception

                Debug.WriteLine(ex.Message)

            Finally
                daoMain.CloseConnection(cnnSearch)

            End Try

            Return dtSearch
        End Function

        Private Function QueryBuilder(ByVal Value As VW_RPT_MONTHLY_RENEWAL_VO, Optional ByVal QueryType As QueryType = 0, _
         Optional ByVal ExcludeFlag As VW_RPT_MONTHLY_RENEWAL_VO.Enum_vw_rpt_monthly_renewal = VW_RPT_MONTHLY_RENEWAL_VO.Enum_vw_rpt_monthly_renewal.NONE, Optional ByVal TableAlias As String = "", Optional ByVal SQLLike As Boolean = False, _
         Optional ByVal IncludeBlanks As Boolean = True) As String

            Dim sbQuery As New StringBuilder
            Dim shrtFlag As Short

            With Value
                If Not isnothing(.CLIENT_NAME) Then shrtFlag += VW_RPT_MONTHLY_RENEWAL_VO.Enum_vw_rpt_monthly_renewal.CLIENT_NAME
                If Not isnothing(.POLICY_NO) Then shrtFlag += VW_RPT_MONTHLY_RENEWAL_VO.Enum_vw_rpt_monthly_renewal.POLICY_NO
                If Not isnothing(.PROVIDER_DESC) Then shrtFlag += VW_RPT_MONTHLY_RENEWAL_VO.Enum_vw_rpt_monthly_renewal.PROVIDER_DESC
                If Not isnothing(.PRDT_DESC) Then shrtFlag += VW_RPT_MONTHLY_RENEWAL_VO.Enum_vw_rpt_monthly_renewal.PRDT_DESC
                If Year(.POLICY_DUE_DT) > 1 Then shrtFlag += VW_RPT_MONTHLY_RENEWAL_VO.Enum_vw_rpt_monthly_renewal.POLICY_DUE_DT
                If Year(.POLICY_DUE_DT_END) > 1 Then shrtFlag += VW_RPT_MONTHLY_RENEWAL_VO.Enum_vw_rpt_monthly_renewal.POLICY_DUE_DT_END
                If .CONTRIBUTION_AMT >= 0 Then shrtFlag += VW_RPT_MONTHLY_RENEWAL_VO.Enum_vw_rpt_monthly_renewal.CONTRIBUTION_AMT
                If Not isnothing(.CURRENCY_DESC) Then shrtFlag += VW_RPT_MONTHLY_RENEWAL_VO.Enum_vw_rpt_monthly_renewal.CURRENCY_DESC
                If Not isnothing(.ADVISOR_CD) Then shrtFlag += VW_RPT_MONTHLY_RENEWAL_VO.Enum_vw_rpt_monthly_renewal.ADVISOR_CD
                If Not isnothing(.FREQUENCY_DESC) Then shrtFlag += VW_RPT_MONTHLY_RENEWAL_VO.Enum_vw_rpt_monthly_renewal.FREQUENCY_DESC
                If Not isnothing(.POLICY_STATUS) Then shrtFlag += VW_RPT_MONTHLY_RENEWAL_VO.Enum_vw_rpt_monthly_renewal.POLICY_STATUS
            End With
            If shrtFlag <> 0 Then
                Dim bytCount As Byte

                If ExcludeFlag <> VW_RPT_MONTHLY_RENEWAL_VO.Enum_vw_rpt_monthly_renewal.None Then shrtFlag -= ExcludeFlag
                With sbQuery

                    Select Case QueryType
                        Case QueryType.SelectType  ' Where
                            .Append("WHERE ")
                        Case QueryType.UpdateType  'Update
                            .Append("UPDATE vw_rpt_monthly_renewal SET ")
                        Case QueryType.InsertType  'Insert
                            .Append("INSERT INTO vw_rpt_monthly_renewal (")
                        Case QueryType.DeleteType   'Delete
                            .Append("DELETE FROM vw_rpt_monthly_renewal WHERE ")
                    End Select

                    Do While shrtFlag > 0
                        If CheckBit(shrtFlag, VW_RPT_MONTHLY_RENEWAL_VO.Enum_vw_rpt_monthly_renewal.CLIENT_NAME) Then
                            .Append("CLIENT_NAME")
                            shrtFlag -= VW_RPT_MONTHLY_RENEWAL_VO.Enum_vw_rpt_monthly_renewal.CLIENT_NAME

                        ElseIf CheckBit(shrtFlag, VW_RPT_MONTHLY_RENEWAL_VO.Enum_vw_rpt_monthly_renewal.POLICY_NO) Then
                            .Append("POLICY_NO")
                            shrtFlag -= VW_RPT_MONTHLY_RENEWAL_VO.Enum_vw_rpt_monthly_renewal.POLICY_NO

                        ElseIf CheckBit(shrtFlag, VW_RPT_MONTHLY_RENEWAL_VO.Enum_vw_rpt_monthly_renewal.PROVIDER_DESC) Then
                            .Append("PROVIDER_DESC")
                            shrtFlag -= VW_RPT_MONTHLY_RENEWAL_VO.Enum_vw_rpt_monthly_renewal.PROVIDER_DESC

                        ElseIf CheckBit(shrtFlag, VW_RPT_MONTHLY_RENEWAL_VO.Enum_vw_rpt_monthly_renewal.PRDT_DESC) Then
                            .Append("PRDT_DESC")
                            shrtFlag -= VW_RPT_MONTHLY_RENEWAL_VO.Enum_vw_rpt_monthly_renewal.PRDT_DESC

                        ElseIf CheckBit(shrtFlag, VW_RPT_MONTHLY_RENEWAL_VO.Enum_vw_rpt_monthly_renewal.POLICY_DUE_DT) Then
                            If CheckBit(shrtFlag, VW_RPT_MONTHLY_RENEWAL_VO.Enum_vw_rpt_monthly_renewal.POLICY_DUE_DT_END) Then
                                shrtFlag -= VW_RPT_MONTHLY_RENEWAL_VO.Enum_vw_rpt_monthly_renewal.POLICY_DUE_DT
                                shrtFlag -= VW_RPT_MONTHLY_RENEWAL_VO.Enum_vw_rpt_monthly_renewal.POLICY_DUE_DT_END
                                .Append(" POLICY_DUE_DT between ? And ? ")
                                QueryType = 4
                            Else
                                .Append("POLICY_DUE_DT")
                                shrtFlag -= VW_RPT_MONTHLY_RENEWAL_VO.Enum_vw_rpt_monthly_renewal.POLICY_DUE_DT

                            End If
                        ElseIf CheckBit(shrtFlag, VW_RPT_MONTHLY_RENEWAL_VO.Enum_vw_rpt_monthly_renewal.CONTRIBUTION_AMT) Then
                            .Append("CONTRIBUTION_AMT")
                            shrtFlag -= VW_RPT_MONTHLY_RENEWAL_VO.Enum_vw_rpt_monthly_renewal.CONTRIBUTION_AMT

                        ElseIf CheckBit(shrtFlag, VW_RPT_MONTHLY_RENEWAL_VO.Enum_vw_rpt_monthly_renewal.CURRENCY_DESC) Then
                            .Append("CURRENCY_DESC")
                            shrtFlag -= VW_RPT_MONTHLY_RENEWAL_VO.Enum_vw_rpt_monthly_renewal.CURRENCY_DESC

                        ElseIf CheckBit(shrtFlag, VW_RPT_MONTHLY_RENEWAL_VO.Enum_vw_rpt_monthly_renewal.ADVISOR_CD) Then
                            .Append("ADVISOR_CD")
                            shrtFlag -= VW_RPT_MONTHLY_RENEWAL_VO.Enum_vw_rpt_monthly_renewal.ADVISOR_CD

                        ElseIf CheckBit(shrtFlag, VW_RPT_MONTHLY_RENEWAL_VO.Enum_vw_rpt_monthly_renewal.FREQUENCY_DESC) Then
                            .Append("FREQUENCY_DESC")
                            shrtFlag -= VW_RPT_MONTHLY_RENEWAL_VO.Enum_vw_rpt_monthly_renewal.FREQUENCY_DESC

                        ElseIf CheckBit(shrtFlag, VW_RPT_MONTHLY_RENEWAL_VO.Enum_vw_rpt_monthly_renewal.POLICY_STATUS) Then
                            .Append("POLICY_STATUS")
                            shrtFlag -= VW_RPT_MONTHLY_RENEWAL_VO.Enum_vw_rpt_monthly_renewal.POLICY_STATUS

                        End If

                        Select Case QueryType
                            Case QueryType.SelectType, QueryType.DeleteType  ' Where Clause
                                Select Case SQLLike
                                    Case False
                                        .Append(" = ? AND ")

                                    Case True
                                        .Append(" LIKE ? AND ")

                                End Select

                            Case QueryType.UpdateType  ' Update
                                .Append(" = ?, ")

                            Case QueryType.InsertType  ' Insert
                                .Append(", ")
                                bytCount += 1

                            Case Else 'bypass
                                .Append(" AND ")
                                QueryType = 0

                        End Select
                    Loop

                    Select Case QueryType
                        Case QueryType.SelectType, QueryType.DeleteType  'Where Clause
                            .Replace(" AND ", "", .Length - 6, 6)

                        Case QueryType.UpdateType   ' Update
                            .Replace("?, ", "? ", .Length - 3, 3)

                        Case QueryType.InsertType  ' Insert
                            .Replace(", ", ") VALUES (", .Length - 2, 2)
                            .Append("#", bytCount)
                            .Replace("#", "?,")
                            .Replace("?,", "?);", .Length - 2, 2)
                    End Select
                End With
            End If
            Return sbQuery.ToString
        End Function

        Private Sub ParamBuilder(ByRef Command As OLEDBCommand, ByVal Value As vw_rpt_monthly_renewal_VO, Optional ByVal QueryType As QueryType = 0, Optional ByVal ExcludeValue As vw_rpt_monthly_renewal_VO.Enum_vw_rpt_monthly_renewal = vw_rpt_monthly_renewal_VO.Enum_vw_rpt_monthly_renewal.None)
            With Command.Parameters
                If Not isnothing(Value.CLIENT_NAME) And Not CheckBit(ExcludeValue, vw_rpt_monthly_renewal_VO.Enum_vw_rpt_monthly_renewal.CLIENT_NAME) Then .AddWithValue("@Client_Name", Value.CLIENT_NAME)
                If Not isnothing(Value.POLICY_NO) And Not CheckBit(ExcludeValue, vw_rpt_monthly_renewal_VO.Enum_vw_rpt_monthly_renewal.POLICY_NO) Then .AddWithValue("@policy_no", Value.POLICY_NO)
                If Not isnothing(Value.PROVIDER_DESC) And Not CheckBit(ExcludeValue, vw_rpt_monthly_renewal_VO.Enum_vw_rpt_monthly_renewal.PROVIDER_DESC) Then .AddWithValue("@provider_desc", Value.PROVIDER_DESC)
                If Not isnothing(Value.PRDT_DESC) And Not CheckBit(ExcludeValue, vw_rpt_monthly_renewal_VO.Enum_vw_rpt_monthly_renewal.PRDT_DESC) Then .AddWithValue("@prdt_desc", Value.PRDT_DESC)
                If Year(Value.POLICY_DUE_DT) > 1 And Not CheckBit(ExcludeValue, vw_rpt_monthly_renewal_VO.Enum_vw_rpt_monthly_renewal.POLICY_DUE_DT) Then .AddWithValue("@policy_due_dt", Value.POLICY_DUE_DT)
                If Year(Value.POLICY_DUE_DT_END) > 1 And Not CheckBit(ExcludeValue, vw_rpt_monthly_renewal_VO.Enum_vw_rpt_monthly_renewal.POLICY_DUE_DT_END) Then .AddWithValue("@policy_due_dt_END", Value.POLICY_DUE_DT_END)
                If Value.CONTRIBUTION_AMT >= 0 And Not CheckBit(ExcludeValue, vw_rpt_monthly_renewal_VO.Enum_vw_rpt_monthly_renewal.CONTRIBUTION_AMT) Then .AddWithValue("@contribution_amt", Value.CONTRIBUTION_AMT)
                If Not isnothing(Value.CURRENCY_DESC) And Not CheckBit(ExcludeValue, vw_rpt_monthly_renewal_VO.Enum_vw_rpt_monthly_renewal.CURRENCY_DESC) Then .AddWithValue("@currency_desc", Value.CURRENCY_DESC)
                If Not isnothing(Value.ADVISOR_CD) And Not CheckBit(ExcludeValue, vw_rpt_monthly_renewal_VO.Enum_vw_rpt_monthly_renewal.ADVISOR_CD) Then .AddWithValue("@Advisor_Cd", Value.ADVISOR_CD)
                If Not isnothing(Value.FREQUENCY_DESC) And Not CheckBit(ExcludeValue, vw_rpt_monthly_renewal_VO.Enum_vw_rpt_monthly_renewal.FREQUENCY_DESC) Then .AddWithValue("@frequency_desc", Value.FREQUENCY_DESC)
                If Not isnothing(Value.POLICY_STATUS) And Not CheckBit(ExcludeValue, vw_rpt_monthly_renewal_VO.Enum_vw_rpt_monthly_renewal.POLICY_STATUS) Then .AddWithValue("@policy_status", Value.POLICY_STATUS)
            End With
        End Sub

    End Class

#End Region

#Region "rpt_mailmerge"

    <Serializable()> _
    Public Class VW_RPT_MAILMERGE_VO

        Public CLIENT_NAME As String
        Public BIRTHDAY As Date
        Public BIRTHDAY_END As Date
        Public COMPLETION_DT As Date
        Public COMPLETION_DT_END As Date
        Public POLICY_NO As String
        Public PROVIDER_DESC As String
        Public PRDT_DESC As String
        Public INSURED_AMT As Double = -1
        Public CORRES_ADDRESS As String
        Public CORRES_ADDRESS2 As String
        Public CITY_DESC As String
        Public COUNTRY_DESC As String
        Public ZIP_CD As String
        Public CORRES_ST As String
        Public ADVISOR As String
        Public ADVISOR_CD As String
        Public CONTRIBUTION_DUE_DT As Date
        Public CONTRIBUTION_DUE_DT_END As Date
        Public CONTRIBUTION_AMT As Double = -1
        Public FREQUENCY_DESC As String
        Public CURRENCY_DESC As String
        Public POLICY_STATUS As String

        Public Enum Enum_vw_rpt_mailmerge As Integer
            NONE = 0
            Client_Name = 1
            birthday = 2
            birthday_END = 4
            Completion_dt = 8
            Completion_dt_END = 16
            policy_no = 32
            provider_desc = 64
            prdt_desc = 128
            insured_amt = 256
            corres_address = 512
            corres_address2 = 1024
            city_desc = 2048
            country_desc = 4096
            zip_cd = 8192
            corres_st = 16384
            Advisor = 32768
            Advisor_Cd = 65536
            contribution_due_dt = 131072
            contribution_due_dt_END = 262144
            contribution_amt = 524288
            frequency_desc = 1048576
            currency_desc = 2097152
            policy_status = 4194304
        End Enum

    End Class

    Public Class VW_RPT_MAILMERGE_DAO
        Inherits DAOMain.CommonDAO

        Public Function Search(ByVal Value As VW_RPT_MAILMERGE_VO, Optional ByVal ExcludeValue As VW_RPT_MAILMERGE_VO.Enum_vw_rpt_mailmerge = 0, Optional ByVal SQLLike As Boolean = False) As Data.DataTable
            Dim daoMain As New Polydao.Common
            Dim cnnSearch As New OleDbConnection
            Dim dtSearch As New Data.DataTable

            Try
                daoMain.ConnectionString = Me.ConnectionString
                cnnSearch = daoMain.OpenConnection

                Dim cmdSearch As New OleDbCommand

                With cmdSearch
                    .Connection = cnnSearch
                    .CommandText = "SELECT * FROM vw_rpt_mailmerge " & QueryBuilder(Value, QueryType.SelectType, ExcludeValue, , SQLLike) & " Order by prdt_desc, Client_Name, contribution_due_dt"
                End With

                Call ParamBuilder(cmdSearch, Value, ExcludeValue)

                Dim daSearch As New OleDbDataAdapter(cmdSearch)

                daSearch.Fill(dtSearch)

            Catch ex As Exception

                Debug.WriteLine(ex.Message)

            Finally
                daoMain.CloseConnection(cnnSearch)

            End Try

            Return dtSearch
        End Function

        Private Function QueryBuilder(ByVal Value As VW_RPT_MAILMERGE_VO, Optional ByVal QueryType As QueryType = 0, _
         Optional ByVal ExcludeFlag As VW_RPT_MAILMERGE_VO.Enum_vw_rpt_mailmerge = VW_RPT_MAILMERGE_VO.Enum_vw_rpt_mailmerge.NONE, Optional ByVal TableAlias As String = "", Optional ByVal SQLLike As Boolean = False, _
         Optional ByVal IncludeBlanks As Boolean = True) As String

            Dim sbQuery As New StringBuilder
            Dim shrtFlag As Integer

            With Value
                If Not IsNothing(.CLIENT_NAME) Then shrtFlag += VW_RPT_MAILMERGE_VO.Enum_vw_rpt_mailmerge.Client_Name
                If Year(.BIRTHDAY) > 1 Then shrtFlag += VW_RPT_MAILMERGE_VO.Enum_vw_rpt_mailmerge.birthday
                If Year(.BIRTHDAY_END) > 1 Then shrtFlag += VW_RPT_MAILMERGE_VO.Enum_vw_rpt_mailmerge.birthday_END
                If Year(.COMPLETION_DT) > 1 Then shrtFlag += VW_RPT_MAILMERGE_VO.Enum_vw_rpt_mailmerge.Completion_dt
                If Year(.COMPLETION_DT_END) > 1 Then shrtFlag += VW_RPT_MAILMERGE_VO.Enum_vw_rpt_mailmerge.Completion_dt_END
                If Not IsNothing(.POLICY_NO) Then shrtFlag += VW_RPT_MAILMERGE_VO.Enum_vw_rpt_mailmerge.policy_no
                If Not IsNothing(.PROVIDER_DESC) Then shrtFlag += VW_RPT_MAILMERGE_VO.Enum_vw_rpt_mailmerge.provider_desc
                If Not IsNothing(.PRDT_DESC) Then shrtFlag += VW_RPT_MAILMERGE_VO.Enum_vw_rpt_mailmerge.prdt_desc
                If .INSURED_AMT >= 0 Then shrtFlag += VW_RPT_MAILMERGE_VO.Enum_vw_rpt_mailmerge.insured_amt
                If Not IsNothing(.CORRES_ADDRESS) Then shrtFlag += VW_RPT_MAILMERGE_VO.Enum_vw_rpt_mailmerge.corres_address
                If Not IsNothing(.CORRES_ADDRESS2) Then shrtFlag += VW_RPT_MAILMERGE_VO.Enum_vw_rpt_mailmerge.corres_address2
                If Not IsNothing(.CITY_DESC) Then shrtFlag += VW_RPT_MAILMERGE_VO.Enum_vw_rpt_mailmerge.city_desc
                If Not IsNothing(.COUNTRY_DESC) Then shrtFlag += VW_RPT_MAILMERGE_VO.Enum_vw_rpt_mailmerge.country_desc
                If Not IsNothing(.ZIP_CD) Then shrtFlag += VW_RPT_MAILMERGE_VO.Enum_vw_rpt_mailmerge.zip_cd
                If Not IsNothing(.CORRES_ST) Then shrtFlag += VW_RPT_MAILMERGE_VO.Enum_vw_rpt_mailmerge.corres_st
                If Not IsNothing(.ADVISOR) Then shrtFlag += VW_RPT_MAILMERGE_VO.Enum_vw_rpt_mailmerge.Advisor
                If Not IsNothing(.ADVISOR_CD) Then shrtFlag += VW_RPT_MAILMERGE_VO.Enum_vw_rpt_mailmerge.Advisor_Cd
                If Year(.CONTRIBUTION_DUE_DT) > 1 Then shrtFlag += VW_RPT_MAILMERGE_VO.Enum_vw_rpt_mailmerge.contribution_due_dt
                If Year(.CONTRIBUTION_DUE_DT_END) > 1 Then shrtFlag += VW_RPT_MAILMERGE_VO.Enum_vw_rpt_mailmerge.contribution_due_dt_END
                If .CONTRIBUTION_AMT >= 0 Then shrtFlag += VW_RPT_MAILMERGE_VO.Enum_vw_rpt_mailmerge.contribution_amt
                If Not IsNothing(.FREQUENCY_DESC) Then shrtFlag += VW_RPT_MAILMERGE_VO.Enum_vw_rpt_mailmerge.frequency_desc
                If Not IsNothing(.CURRENCY_DESC) Then shrtFlag += VW_RPT_MAILMERGE_VO.Enum_vw_rpt_mailmerge.currency_desc
                If Not IsNothing(.POLICY_STATUS) Then shrtFlag += VW_RPT_MAILMERGE_VO.Enum_vw_rpt_mailmerge.policy_status
            End With
            If shrtFlag <> 0 Then
                Dim bytCount As Byte

                If ExcludeFlag <> VW_RPT_MAILMERGE_VO.Enum_vw_rpt_mailmerge.NONE Then shrtFlag -= ExcludeFlag
                With sbQuery

                    Select Case QueryType
                        Case QueryType.SelectType  ' Where
                            .Append("WHERE ")
                        Case QueryType.UpdateType  'Update
                            .Append("UPDATE vw_rpt_mailmerge SET ")
                        Case QueryType.InsertType  'Insert
                            .Append("INSERT INTO vw_rpt_mailmerge (")
                        Case QueryType.DeleteType   'Delete
                            .Append("DELETE FROM vw_rpt_mailmerge WHERE ")
                    End Select

                    Do While shrtFlag > 0
                        If CheckBit(shrtFlag, VW_RPT_MAILMERGE_VO.Enum_vw_rpt_mailmerge.Client_Name) Then
                            .Append("CLIENT_NAME")
                            shrtFlag -= VW_RPT_MAILMERGE_VO.Enum_vw_rpt_mailmerge.Client_Name

                        ElseIf CheckBit(shrtFlag, VW_RPT_MAILMERGE_VO.Enum_vw_rpt_mailmerge.birthday) Then
                            If CheckBit(shrtFlag, VW_RPT_MAILMERGE_VO.Enum_vw_rpt_mailmerge.birthday_END) Then
                                shrtFlag -= VW_RPT_MAILMERGE_VO.Enum_vw_rpt_mailmerge.birthday
                                shrtFlag -= VW_RPT_MAILMERGE_VO.Enum_vw_rpt_mailmerge.birthday_END
                                .Append(" BIRTHDAY between ? And ? ")
                                QueryType = 4
                            Else
                                .Append("BIRTHDAY")
                                shrtFlag -= VW_RPT_MAILMERGE_VO.Enum_vw_rpt_mailmerge.birthday

                            End If
                        ElseIf CheckBit(shrtFlag, VW_RPT_MAILMERGE_VO.Enum_vw_rpt_mailmerge.Completion_dt) Then
                            If CheckBit(shrtFlag, VW_RPT_MAILMERGE_VO.Enum_vw_rpt_mailmerge.Completion_dt_END) Then
                                shrtFlag -= VW_RPT_MAILMERGE_VO.Enum_vw_rpt_mailmerge.Completion_dt
                                shrtFlag -= VW_RPT_MAILMERGE_VO.Enum_vw_rpt_mailmerge.Completion_dt_END
                                .Append(" COMPLETION_DT between ? And ? ")
                                QueryType = 4
                            Else
                                .Append("COMPLETION_DT")
                                shrtFlag -= VW_RPT_MAILMERGE_VO.Enum_vw_rpt_mailmerge.Completion_dt

                            End If
                        ElseIf CheckBit(shrtFlag, VW_RPT_MAILMERGE_VO.Enum_vw_rpt_mailmerge.policy_no) Then
                            .Append("POLICY_NO")
                            shrtFlag -= VW_RPT_MAILMERGE_VO.Enum_vw_rpt_mailmerge.policy_no

                        ElseIf CheckBit(shrtFlag, VW_RPT_MAILMERGE_VO.Enum_vw_rpt_mailmerge.provider_desc) Then
                            .Append("PROVIDER_DESC")
                            shrtFlag -= VW_RPT_MAILMERGE_VO.Enum_vw_rpt_mailmerge.provider_desc

                        ElseIf CheckBit(shrtFlag, VW_RPT_MAILMERGE_VO.Enum_vw_rpt_mailmerge.prdt_desc) Then
                            .Append("PRDT_DESC")
                            shrtFlag -= VW_RPT_MAILMERGE_VO.Enum_vw_rpt_mailmerge.prdt_desc

                        ElseIf CheckBit(shrtFlag, VW_RPT_MAILMERGE_VO.Enum_vw_rpt_mailmerge.insured_amt) Then
                            .Append("INSURED_AMT")
                            shrtFlag -= VW_RPT_MAILMERGE_VO.Enum_vw_rpt_mailmerge.insured_amt

                        ElseIf CheckBit(shrtFlag, VW_RPT_MAILMERGE_VO.Enum_vw_rpt_mailmerge.corres_address) Then
                            .Append("CORRES_ADDRESS")
                            shrtFlag -= VW_RPT_MAILMERGE_VO.Enum_vw_rpt_mailmerge.corres_address

                        ElseIf CheckBit(shrtFlag, VW_RPT_MAILMERGE_VO.Enum_vw_rpt_mailmerge.corres_address2) Then
                            .Append("CORRES_ADDRESS2")
                            shrtFlag -= VW_RPT_MAILMERGE_VO.Enum_vw_rpt_mailmerge.corres_address2

                        ElseIf CheckBit(shrtFlag, VW_RPT_MAILMERGE_VO.Enum_vw_rpt_mailmerge.city_desc) Then
                            .Append("CITY_DESC")
                            shrtFlag -= VW_RPT_MAILMERGE_VO.Enum_vw_rpt_mailmerge.city_desc

                        ElseIf CheckBit(shrtFlag, VW_RPT_MAILMERGE_VO.Enum_vw_rpt_mailmerge.country_desc) Then
                            .Append("COUNTRY_DESC")
                            shrtFlag -= VW_RPT_MAILMERGE_VO.Enum_vw_rpt_mailmerge.country_desc

                        ElseIf CheckBit(shrtFlag, VW_RPT_MAILMERGE_VO.Enum_vw_rpt_mailmerge.zip_cd) Then
                            .Append("ZIP_CD")
                            shrtFlag -= VW_RPT_MAILMERGE_VO.Enum_vw_rpt_mailmerge.zip_cd

                        ElseIf CheckBit(shrtFlag, VW_RPT_MAILMERGE_VO.Enum_vw_rpt_mailmerge.corres_st) Then
                            .Append("CORRES_ST")
                            shrtFlag -= VW_RPT_MAILMERGE_VO.Enum_vw_rpt_mailmerge.corres_st

                        ElseIf CheckBit(shrtFlag, VW_RPT_MAILMERGE_VO.Enum_vw_rpt_mailmerge.Advisor) Then
                            .Append("ADVISOR")
                            shrtFlag -= VW_RPT_MAILMERGE_VO.Enum_vw_rpt_mailmerge.Advisor

                        ElseIf CheckBit(shrtFlag, VW_RPT_MAILMERGE_VO.Enum_vw_rpt_mailmerge.Advisor_Cd) Then
                            .Append("ADVISOR_CD")
                            shrtFlag -= VW_RPT_MAILMERGE_VO.Enum_vw_rpt_mailmerge.Advisor_Cd

                        ElseIf CheckBit(shrtFlag, VW_RPT_MAILMERGE_VO.Enum_vw_rpt_mailmerge.contribution_due_dt) Then
                            If CheckBit(shrtFlag, VW_RPT_MAILMERGE_VO.Enum_vw_rpt_mailmerge.contribution_due_dt_END) Then
                                shrtFlag -= VW_RPT_MAILMERGE_VO.Enum_vw_rpt_mailmerge.contribution_due_dt
                                shrtFlag -= VW_RPT_MAILMERGE_VO.Enum_vw_rpt_mailmerge.contribution_due_dt_END
                                .Append(" CONTRIBUTION_DUE_DT between ? And ? ")
                                QueryType = 4
                            Else
                                .Append("CONTRIBUTION_DUE_DT")
                                shrtFlag -= VW_RPT_MAILMERGE_VO.Enum_vw_rpt_mailmerge.contribution_due_dt

                            End If
                        ElseIf CheckBit(shrtFlag, VW_RPT_MAILMERGE_VO.Enum_vw_rpt_mailmerge.contribution_amt) Then
                            .Append("CONTRIBUTION_AMT")
                            shrtFlag -= VW_RPT_MAILMERGE_VO.Enum_vw_rpt_mailmerge.contribution_amt

                        ElseIf CheckBit(shrtFlag, VW_RPT_MAILMERGE_VO.Enum_vw_rpt_mailmerge.frequency_desc) Then
                            .Append("FREQUENCY_DESC")
                            shrtFlag -= VW_RPT_MAILMERGE_VO.Enum_vw_rpt_mailmerge.frequency_desc

                        ElseIf CheckBit(shrtFlag, VW_RPT_MAILMERGE_VO.Enum_vw_rpt_mailmerge.currency_desc) Then
                            .Append("CURRENCY_DESC")
                            shrtFlag -= VW_RPT_MAILMERGE_VO.Enum_vw_rpt_mailmerge.currency_desc

                        ElseIf CheckBit(shrtFlag, VW_RPT_MAILMERGE_VO.Enum_vw_rpt_mailmerge.policy_status) Then
                            .Append("POLICY_STATUS")
                            shrtFlag -= VW_RPT_MAILMERGE_VO.Enum_vw_rpt_mailmerge.policy_status

                        End If

                        Select Case QueryType
                            Case QueryType.SelectType, QueryType.DeleteType  ' Where Clause
                                Select Case SQLLike
                                    Case False
                                        .Append(" = ? AND ")

                                    Case True
                                        .Append(" LIKE ? AND ")

                                End Select

                            Case QueryType.UpdateType  ' Update
                                .Append(" = ?, ")

                            Case QueryType.InsertType  ' Insert
                                .Append(", ")
                                bytCount += 1

                            Case Else 'bypass
                                .Append(" AND ")
                                QueryType = 0

                        End Select
                    Loop

                    Select Case QueryType
                        Case QueryType.SelectType, QueryType.DeleteType  'Where Clause
                            .Replace(" AND ", "", .Length - 6, 6)

                        Case QueryType.UpdateType   ' Update
                            .Replace("?, ", "? ", .Length - 3, 3)

                        Case QueryType.InsertType  ' Insert
                            .Replace(", ", ") VALUES (", .Length - 2, 2)
                            .Append("#", bytCount)
                            .Replace("#", "?,")
                            .Replace("?,", "?);", .Length - 2, 2)
                    End Select
                End With
            End If
            Return sbQuery.ToString
        End Function

        Private Sub ParamBuilder(ByRef Command As OleDbCommand, ByVal Value As VW_RPT_MAILMERGE_VO, Optional ByVal QueryType As QueryType = 0, Optional ByVal ExcludeValue As VW_RPT_MAILMERGE_VO.Enum_vw_rpt_mailmerge = VW_RPT_MAILMERGE_VO.Enum_vw_rpt_mailmerge.NONE)
            With Command.Parameters
                If Not IsNothing(Value.CLIENT_NAME) And Not CheckBit(ExcludeValue, VW_RPT_MAILMERGE_VO.Enum_vw_rpt_mailmerge.Client_Name) Then .AddWithValue("@Client_Name", Value.CLIENT_NAME)
                If Year(Value.BIRTHDAY) > 1 And Not CheckBit(ExcludeValue, VW_RPT_MAILMERGE_VO.Enum_vw_rpt_mailmerge.birthday) Then .AddWithValue("@birthday", Value.BIRTHDAY)
                If Year(Value.BIRTHDAY_END) > 1 And Not CheckBit(ExcludeValue, VW_RPT_MAILMERGE_VO.Enum_vw_rpt_mailmerge.birthday_END) Then .AddWithValue("@birthday_END", Value.BIRTHDAY_END)
                If Year(Value.COMPLETION_DT) > 1 And Not CheckBit(ExcludeValue, VW_RPT_MAILMERGE_VO.Enum_vw_rpt_mailmerge.Completion_dt) Then .AddWithValue("@Completion_dt", Value.COMPLETION_DT)
                If Year(Value.COMPLETION_DT_END) > 1 And Not CheckBit(ExcludeValue, VW_RPT_MAILMERGE_VO.Enum_vw_rpt_mailmerge.Completion_dt_END) Then .AddWithValue("@Completion_dt_END", Value.COMPLETION_DT_END)
                If Not IsNothing(Value.POLICY_NO) And Not CheckBit(ExcludeValue, VW_RPT_MAILMERGE_VO.Enum_vw_rpt_mailmerge.policy_no) Then .AddWithValue("@policy_no", Value.POLICY_NO)
                If Not IsNothing(Value.PROVIDER_DESC) And Not CheckBit(ExcludeValue, VW_RPT_MAILMERGE_VO.Enum_vw_rpt_mailmerge.provider_desc) Then .AddWithValue("@provider_desc", Value.PROVIDER_DESC)
                If Not IsNothing(Value.PRDT_DESC) And Not CheckBit(ExcludeValue, VW_RPT_MAILMERGE_VO.Enum_vw_rpt_mailmerge.prdt_desc) Then .AddWithValue("@prdt_desc", Value.PRDT_DESC)
                If Value.INSURED_AMT >= 0 And Not CheckBit(ExcludeValue, VW_RPT_MAILMERGE_VO.Enum_vw_rpt_mailmerge.insured_amt) Then .AddWithValue("@insured_amt", Value.INSURED_AMT)
                If Not IsNothing(Value.CORRES_ADDRESS) And Not CheckBit(ExcludeValue, VW_RPT_MAILMERGE_VO.Enum_vw_rpt_mailmerge.corres_address) Then .AddWithValue("@corres_address", Value.CORRES_ADDRESS)
                If Not IsNothing(Value.CORRES_ADDRESS2) And Not CheckBit(ExcludeValue, VW_RPT_MAILMERGE_VO.Enum_vw_rpt_mailmerge.corres_address2) Then .AddWithValue("@corres_address2", Value.CORRES_ADDRESS2)
                If Not IsNothing(Value.CITY_DESC) And Not CheckBit(ExcludeValue, VW_RPT_MAILMERGE_VO.Enum_vw_rpt_mailmerge.city_desc) Then .AddWithValue("@city_desc", Value.CITY_DESC)
                If Not IsNothing(Value.COUNTRY_DESC) And Not CheckBit(ExcludeValue, VW_RPT_MAILMERGE_VO.Enum_vw_rpt_mailmerge.country_desc) Then .AddWithValue("@country_desc", Value.COUNTRY_DESC)
                If Not IsNothing(Value.ZIP_CD) And Not CheckBit(ExcludeValue, VW_RPT_MAILMERGE_VO.Enum_vw_rpt_mailmerge.zip_cd) Then .AddWithValue("@zip_cd", Value.ZIP_CD)
                If Not IsNothing(Value.CORRES_ST) And Not CheckBit(ExcludeValue, VW_RPT_MAILMERGE_VO.Enum_vw_rpt_mailmerge.corres_st) Then .AddWithValue("@corres_st", Value.CORRES_ST)
                If Not IsNothing(Value.ADVISOR) And Not CheckBit(ExcludeValue, VW_RPT_MAILMERGE_VO.Enum_vw_rpt_mailmerge.Advisor) Then .AddWithValue("@Advisor", Value.ADVISOR)
                If Not IsNothing(Value.ADVISOR_CD) And Not CheckBit(ExcludeValue, VW_RPT_MAILMERGE_VO.Enum_vw_rpt_mailmerge.Advisor_Cd) Then .AddWithValue("@Advisor_Cd", Value.ADVISOR_CD)
                If Year(Value.CONTRIBUTION_DUE_DT) > 1 And Not CheckBit(ExcludeValue, VW_RPT_MAILMERGE_VO.Enum_vw_rpt_mailmerge.contribution_due_dt) Then .AddWithValue("@contribution_due_dt", Value.CONTRIBUTION_DUE_DT)
                If Year(Value.CONTRIBUTION_DUE_DT_END) > 1 And Not CheckBit(ExcludeValue, VW_RPT_MAILMERGE_VO.Enum_vw_rpt_mailmerge.contribution_due_dt_END) Then .AddWithValue("@contribution_due_dt_END", Value.CONTRIBUTION_DUE_DT_END)
                If Value.CONTRIBUTION_AMT >= 0 And Not CheckBit(ExcludeValue, VW_RPT_MAILMERGE_VO.Enum_vw_rpt_mailmerge.contribution_amt) Then .AddWithValue("@contribution_amt", Value.CONTRIBUTION_AMT)
                If Not IsNothing(Value.FREQUENCY_DESC) And Not CheckBit(ExcludeValue, VW_RPT_MAILMERGE_VO.Enum_vw_rpt_mailmerge.frequency_desc) Then .AddWithValue("@frequency_desc", Value.FREQUENCY_DESC)
                If Not IsNothing(Value.CURRENCY_DESC) And Not CheckBit(ExcludeValue, VW_RPT_MAILMERGE_VO.Enum_vw_rpt_mailmerge.currency_desc) Then .AddWithValue("@currency_desc", Value.CURRENCY_DESC)
                If Not IsNothing(Value.POLICY_STATUS) And Not CheckBit(ExcludeValue, VW_RPT_MAILMERGE_VO.Enum_vw_rpt_mailmerge.policy_status) Then .AddWithValue("@policy_status", Value.POLICY_STATUS)
            End With
        End Sub

    End Class

#End Region

#Region "vw_data_mine_struc"

    <Serializable()> _
    Public Class VW_DATA_MINE_STRUC_VO

        Public COL_ID As String
        Public COL_DESC As String
        Public VB_TYPE As String

        Public Enum Enum_VW_DATA_MINE_STRUC As Short
            NONE = 0
            col_id = 1
            col_desc = 2
            vb_type = 4
        End Enum

    End Class

    Public Class VW_DATA_MINE_STRUC_DAO
        Inherits CommonDAO

        Public Function Search(ByVal Value As VW_DATA_MINE_STRUC_VO, Optional ByVal ExcludeValue As VW_DATA_MINE_STRUC_VO.Enum_VW_DATA_MINE_STRUC = 0, Optional ByVal SQLLike As Boolean = False) As Data.DataTable
            Dim daoMain As New Polydao.Common
            Dim cnnSearch As New OleDbConnection
            Dim dtSearch As New Data.DataTable

            Try
                daoMain.ConnectionString = Me.ConnectionString
                cnnSearch = daoMain.OpenConnection

                Dim cmdSearch As New OleDbCommand

                With cmdSearch
                    .Connection = cnnSearch
                    .CommandText = Replace("SELECT * FROM vw_data_mine_struc " & QueryBuilder(Value, QueryType.SelectType, ExcludeValue, , SQLLike), ";", "") & _
                            " Order by convert(int, colorder);"
                End With

                Call ParamBuilder(cmdSearch, Value, ExcludeValue)

                Dim daSearch As New OleDbDataAdapter(cmdSearch)

                daSearch.Fill(dtSearch)

            Catch ex As Exception

                Debug.WriteLine(ex.Message)

            Finally
                daoMain.CloseConnection(cnnSearch)

            End Try

            Return dtSearch
        End Function

        Private Function QueryBuilder(ByVal Value As VW_DATA_MINE_STRUC_VO, Optional ByVal QueryType As QueryType = 0, _
         Optional ByVal ExcludeFlag As VW_DATA_MINE_STRUC_VO.Enum_VW_DATA_MINE_STRUC = VW_DATA_MINE_STRUC_VO.Enum_VW_DATA_MINE_STRUC.NONE, Optional ByVal TableAlias As String = "", Optional ByVal SQLLike As Boolean = False, _
         Optional ByVal IncludeBlanks As Boolean = True) As String

            Dim sbQuery As New StringBuilder
            Dim shrtFlag As Short

            With Value
                If Not IsNothing(.COL_ID) Then shrtFlag += VW_DATA_MINE_STRUC_VO.Enum_VW_DATA_MINE_STRUC.col_id
                If Not IsNothing(.COL_DESC) Then shrtFlag += VW_DATA_MINE_STRUC_VO.Enum_VW_DATA_MINE_STRUC.col_desc
                If Not IsNothing(.VB_TYPE) Then shrtFlag += VW_DATA_MINE_STRUC_VO.Enum_VW_DATA_MINE_STRUC.vb_type
            End With
            If shrtFlag <> 0 Then
                Dim bytCount As Byte

                If ExcludeFlag <> VW_DATA_MINE_STRUC_VO.Enum_VW_DATA_MINE_STRUC.NONE Then shrtFlag -= ExcludeFlag
                With sbQuery

                    Select Case QueryType
                        Case QueryType.SelectType  ' Where
                            .Append("WHERE ")
                        Case QueryType.UpdateType  'Update
                            .Append("UPDATE vw_data_mine_struc SET ")
                        Case QueryType.InsertType  'Insert
                            .Append("INSERT INTO vw_data_mine_struc (")
                        Case QueryType.DeleteType   'Delete
                            .Append("DELETE FROM vw_data_mine_struc WHERE ")
                    End Select

                    Do While shrtFlag > 0
                        If CheckBit(shrtFlag, VW_DATA_MINE_STRUC_VO.Enum_VW_DATA_MINE_STRUC.col_id) Then
                            .Append("COL_ID")
                            shrtFlag -= VW_DATA_MINE_STRUC_VO.Enum_VW_DATA_MINE_STRUC.col_id

                        ElseIf CheckBit(shrtFlag, VW_DATA_MINE_STRUC_VO.Enum_VW_DATA_MINE_STRUC.col_desc) Then
                            .Append("COL_DESC")
                            shrtFlag -= VW_DATA_MINE_STRUC_VO.Enum_VW_DATA_MINE_STRUC.col_desc

                        ElseIf CheckBit(shrtFlag, VW_DATA_MINE_STRUC_VO.Enum_VW_DATA_MINE_STRUC.vb_type) Then
                            .Append("VB_TYPE")
                            shrtFlag -= VW_DATA_MINE_STRUC_VO.Enum_VW_DATA_MINE_STRUC.vb_type

                        End If

                        Select Case QueryType
                            Case QueryType.SelectType, QueryType.DeleteType  ' Where Clause
                                Select Case SQLLike
                                    Case False
                                        .Append(" = ? AND ")

                                    Case True
                                        .Append(" LIKE ? AND ")

                                End Select

                            Case QueryType.UpdateType  ' Update
                                .Append(" = ?, ")

                            Case QueryType.InsertType  ' Insert
                                .Append(", ")
                                bytCount += 1

                            Case Else 'bypass
                                .Append(" AND ")
                                QueryType = 0

                        End Select
                    Loop

                    Select Case QueryType
                        Case QueryType.SelectType, QueryType.DeleteType  'Where Clause
                            .Replace(" AND ", "", .Length - 6, 6)
                            .Append(";")

                        Case QueryType.UpdateType   ' Update
                            .Replace("?, ", "? ", .Length - 3, 3)

                        Case QueryType.InsertType  ' Insert
                            .Replace(", ", ") VALUES (", .Length - 2, 2)
                            .Append("#", bytCount)
                            .Replace("#", "?,")
                            .Replace("?,", "?);", .Length - 2, 2)
                    End Select
                End With
            End If
            Return sbQuery.ToString
        End Function

        Private Sub ParamBuilder(ByRef Command As OleDbCommand, ByVal Value As VW_DATA_MINE_STRUC_VO, Optional ByVal QueryType As QueryType = 0, Optional ByVal ExcludeValue As VW_DATA_MINE_STRUC_VO.Enum_VW_DATA_MINE_STRUC = VW_DATA_MINE_STRUC_VO.Enum_VW_DATA_MINE_STRUC.NONE)
            With Command.Parameters
                If Not IsNothing(Value.COL_ID) And Not CheckBit(ExcludeValue, VW_DATA_MINE_STRUC_VO.Enum_VW_DATA_MINE_STRUC.col_id) Then .AddWithValue("@col_id", Value.COL_ID)
                If Not IsNothing(Value.COL_DESC) And Not CheckBit(ExcludeValue, VW_DATA_MINE_STRUC_VO.Enum_VW_DATA_MINE_STRUC.col_desc) Then .AddWithValue("@col_desc", Value.COL_DESC)
                If Not IsNothing(Value.VB_TYPE) And Not CheckBit(ExcludeValue, VW_DATA_MINE_STRUC_VO.Enum_VW_DATA_MINE_STRUC.vb_type) Then .AddWithValue("@vb_type", Value.VB_TYPE)
            End With
        End Sub
        Public Function GetQuery(ByVal Value As VW_DATA_MINE_STRUC_VO, Optional ByVal ExcludeValue As VW_DATA_MINE_STRUC_VO.Enum_VW_DATA_MINE_STRUC = 0, Optional ByVal SQLLike As Boolean = False) As String
            Dim strReturn As String = "SELECT * FROM vw_data_mine_struc " & QueryBuilder(Value, QueryType.SelectType, ExcludeValue, , SQLLike)
            Return strReturn
        End Function

    End Class

#End Region


End Namespace
